Batch delete – SQL syntax not there

Topic

New Reply

I am trying to delete all records where edits were not applied. i tried this:

Code:

DELETE [Table A].*, [Table B].[Form Dln]
FROM [Table A] LEFT JOIN qryEditRUGItems ON [Table A.FORM_DLN = qryEditRUGItems.[Form Dln]
WHERE (((qryEditRUGItems.[Form Dln]) Is Null));

And access just won’t do it: Cannot delete from table. this occurs when all forms are closed, so I suspect there’s some issue with the syntax. how do you do a batch delete + find unmatched type query?

I tried a WHERE EXISTS but am not very familiar with this so the fact that it didn’t work is not too surprising….I’ll keep working on it, just thought i’d throw it out to the lounge for your viewing pleasure…

TIA

Reply | Quote

Replies

Where does Table B come into this?

Reply | Quote

Table B is in the same Access file. also, i don’t have to reference a query in the join as the edited records are actually in another table. so you can also read the code as

Code:

DELETE [Table A].*, [Table B].[Form Dln]
FROM [Table A] LEFT JOIN [Table B] ON [Table A].FORM_DLN = [Table B].[Form Dln]
WHERE ((([Table B].[Form Dln]) Is Null));

which also does not work

Reply | Quote

Try

DELETE * FROM [Table A] WHERE FORM_DLN Not In (SELECT [Form Dln] FROM [Table B])

Reply | Quote

Thanks! that seems to have done it.

Reply | Quote