Link to home
Start Free TrialLog in
Avatar of ExpExchHelp
ExpExchHelpFlag for United States of America

asked on

Simple Delete Query

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
Avatar of mbizup
mbizup
Flag of Kazakhstan image

For access the first  line shoud just be

DELETE *
delete from tablea where field1 in (select field1 from tableb)
ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
DELETE TableA.*
FROM TableA INNER JOIN TableB ON TableA.Field1 = TableB.Field1

Open in new window