Delete query based on table?

[Support]

Is it possible to use a table to delete the contents from another table?
I should explain further:
I have a table of users including addresses that has a combination primary
key of firstname/ lastname and postcode. Let's call this Table1
I have another table of users who need to be deleted from Table1. They are
in Table2
Both table have the same structure however the contents of Table2 are only
complete on the combined primary key of firstname/ lastname and postcode.
is this possible?
thanks

 

[Michel Walsh]

Hi,


Yes, sure.


To delete the records in ta that are in tb:

DELETE DISTINCTROW ta.* FROM ta INNER JOIN tb ON ta.key - tb.key


or


DELETE ta.* FROM ta WHERE ta.key IN( SELECT tb.key FROM tb)



When you delete over a join, in Jet, add the keyword DISTINCTROW


Hoping it may help,
Vanderghast, Access MVP

 

[Support]

excellent thank you very much.

Hi,


Yes, sure.


To delete the records in ta that are in tb:

DELETE DISTINCTROW ta.* FROM ta INNER JOIN tb ON ta.key - tb.key


or


DELETE ta.* FROM ta WHERE ta.key IN( SELECT tb.key FROM tb)



When you delete over a join, in Jet, add the keyword DISTINCTROW


Hoping it may help,
Vanderghast, Access MVP