Delete from 1 if there is no record in many

[Song Su]

I have 2 tables. Department (one) linked to Phone Book (many). I want to
delete record in Department table where there is no record in Phone Book
table.

DELETE Department.*, [Phone Book].Department
FROM Department LEFT JOIN [Phone Book] ON Department.Department = [Phone
Book].Department
WHERE ((([Phone Book].Department) Is Null));

Error message is:" could delete from specified tables"

How to fix it? Thanks.

 

[Allen Browne]

Try something like this:

DELETE FROM Department
WHERE NOT EXISTS
(SELECT [Phone Book].Department
FROM [Phone Book]
WHERE [Phone Book].Department = Department.Department);

If subqueries are new, here's a starting point:
http://allenbrowne.com/subquery-01.html

 

[Song Su]

Works great! Thanks.

Try something like this:

DELETE FROM Department
WHERE NOT EXISTS
(SELECT [Phone Book].Department
FROM [Phone Book]
WHERE [Phone Book].Department = Department.Department);

If subqueries are new, here's a starting point:
http://allenbrowne.com/subquery-01.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have 2 tables. Department (one) linked to Phone Book (many). I want to
delete record in Department table where there is no record in Phone Book
table.

DELETE Department.*, [Phone Book].Department
FROM Department LEFT JOIN [Phone Book] ON Department.Department = [Phone
Book].Department
WHERE ((([Phone Book].Department) Is Null));

Error message is:" could delete from specified tables"

How to fix it? Thanks.