Delete query based on table?

  • Thread starter Thread starter Support
  • Start date Start date
S

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
 
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
 
excellent thank you very much.

Michel Walsh said:
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
Click to expand...
 
Back
Top