Delete records that match criteria in another table

[Frank]

Hi Folks,
This should be very simple and I don't understand why it's giving me trouble.

I'm trying to delete records from a tbl whose field matches a "bookname"
specified in another 'exclusion' table.

DELETE [WORKING TABLE].*, [WORKING TABLE].BOOK
FROM [WORKING TABLE] INNER JOIN [Tbl-MuniBooksToExclude] ON [WORKING
TABLE].BOOK = [Tbl-MuniBooksToExclude].Bookname
WHERE ((([WORKING TABLE].BOOK)=[Tbl-MuniBooksToExclude]![Bookname]));

This tells me 'could not delete from specified table'. None of these tables
or the DB is locked or read only.
Any advice is appreciated.
Frank

 

[Allen Browne]

Try a subquery like this:

DELETE FROM [WORKING TABLE]
WHERE EXISTS
(SELECT [Tbl-MuniBooksToExclude].Bookname
FROM [Tbl-MuniBooksToExclude]
WHERE [WORKING TABLE].BOOK = [Tbl-MuniBooksToExclude].Bookname);

If subqueries are new, see:
http://allenbrowne.com/subquery-01.html

 

[Frank]

Thanks Allen,
I got this and the original to work with some modification. Looks like the
exclusion table criteria field needs to be a primary key or neither will
work.

DELETE Exists
(SELECT [TExclude].Bookname FROM [TExclude] WHERE [T1].BOOK =
[TExclude].Bookname)
AS Expr1, [T1].*
FROM [T1] INNER JOIN [TExclude] ON [T1].BOOK = [TExclude].Bookname
WHERE
(((Exists (SELECT [TExclude].Bookname FROM [TExclude] WHERE [T1].BOOK =
[Texclude].bookname))<>False));

Thank you!

Try a subquery like this:

DELETE FROM [WORKING TABLE]
WHERE EXISTS
(SELECT [Tbl-MuniBooksToExclude].Bookname
FROM [Tbl-MuniBooksToExclude]
WHERE [WORKING TABLE].BOOK = [Tbl-MuniBooksToExclude].Bookname);

If subqueries are new, see:
http://allenbrowne.com/subquery-01.html

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

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

Hi Folks,
This should be very simple and I don't understand why it's giving me
trouble.

I'm trying to delete records from a tbl whose field matches a "bookname"
specified in another 'exclusion' table.

DELETE [WORKING TABLE].*, [WORKING TABLE].BOOK
FROM [WORKING TABLE] INNER JOIN [Tbl-MuniBooksToExclude] ON [WORKING
TABLE].BOOK = [Tbl-MuniBooksToExclude].Bookname
WHERE ((([WORKING TABLE].BOOK)=[Tbl-MuniBooksToExclude]![Bookname]));

This tells me 'could not delete from specified table'. None of these
tables
or the DB is locked or read only.
Any advice is appreciated.
Frank