Simple Delete Query

ExpExchHelp
ExpExchHelp used Ask the Experts™
on
I'm trying to figure out how to create a simple DELETE query in Access.

Assuming we have two structurally identical tables, I want to delete records (from Table A) that exist in both Table A and Table B.

See attached JPG for a basic example (where records 89 and 17 should be deleted from Table A).

I chose the following SQL but it results in an error "Specify the table containing the records you want to delete).

DELETE TableA.Field1
FROM TableA INNER JOIN TableB ON TableA.Field1 = TableB.Field1
WHERE (((TableA.Field1)=(SELECT TableA.Field1 FROM TableA INNER JOIN TableB ON TableA.Field1 = TableB.Field1;)));


What's the proper syntax?

Thanks,
EEH
Example.jpg
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
mbizupNerd
Most Valuable Expert 2012
Top Expert 2013

Commented:
For access the first  line shoud just be

DELETE *
ramromconsultant

Commented:
delete from tablea where field1 in (select field1 from tableb)
Top Expert 2016
Commented:
try this

DELETE TableA.*
FROM TableA
Where TableA.Field1 In (select  Field1 FROM TableB)
Top Expert 2014

Commented:
DELETE TableA.*
FROM TableA INNER JOIN TableB ON TableA.Field1 = TableB.Field1

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial