How to delete non matching records

[Vinod]

Hi All,

I've 2 tables, tblMain with some countries and tblSub with some countries.
I'd liket to delete non matching countries from tblMain by comparing with
tblSub countries.

Please help me out by sharing your thoughts and ideas.

Advanced Thanks,
Vinod

 

[Douglas J. Steele]

The SQL of the Delete query would be something like:

DELETE FROM tblMain
WHERE Country NOT IN
(SELECT DISTINCT Country FROM tblSub)

 

[Vinod]

Thanks Douglas for your response and your solution is working.

Now I'd like to delete matching fields country and state from tblMain by
comparing with same fields in tblSub.

Could you please answer for my above question.
Thanks
Vinod

 

[Allen Browne]

Doug is suggesting using a subquery. If you're not sure how to extend his
answer, here's an introduction to give you an understanding of how it works:
http://allenbrowne.com/subquery-01.html#DeleteUnmatched

You will probably end up with this kind of thing:
DELETE FROM tblMain
WHERE NOT EXISTS
(SELECT Country FROM tblSub
WHERE tblSub.Country = tblMain.Country
AND tblSub.State = tblMain.State
AND ...);