Delete Query with Joins problem

  • Thread starter Thread starter sweetlisa1217 via AccessMonster.com
  • Start date Start date
S

sweetlisa1217 via AccessMonster.com

How do I delete records in a table with multiple joins?

I have 2 tables..... Table A contains the identified records to be deleted.
Table B contains all records

I only want to delete the records from Table B, where TableA.Field 1 = TableB.
Field 1 AND TableA.Field 2 = TableB.Field 2. I'm not having any luck using
Inner Joins, I get the message "Could Not Delete From Specified Tables"

DELETE TableB.*
FROM TableB
INNER JOIN TableA ON ((TableB.Field1 = TableA.Field1) AND (TableB.Field2 =
TableA.Field2))
 
One way to get around this is to make this an update query and then apply a
delete query on Table A only. For example, the update query with the logic
you've established would change TableA.Field1 to "DELETE ME". Then your
delete query would delete all such records in Table A. I know, this is not
the best answer, but it works.
 
The UPDATE and DELETE query worked like a charm for me. Brilliant in it's simplicity. I kept trying other types of DELETES without success.
 
Back
Top