Phil Parkin - Monday, February 19, 2018 8:23 AM

xxx-593414 - Monday, February 19, 2018 8:02 AM

I have two Tables (In Ms Sql Server)
Table Name           Column Names
MasterCities          Countryy    City
VisitedCities           Country     City

I want to delete from the MasterCities table any Country/Cities I have visited
I don't have any primary keys as the second table is coming from an outside source so need to be able to match by Coountry and City in both tables
So I want to end up , in the MasterCities table, all the Countries/Cities that originally exist in the Master Table, except those  that are in the VisitedCities
Have tried various selects where not in etc. but cannot get syntax right or its deleting too much stuff

Something like this (untested)
delete mc
from MasterCities mc
where exists (select 1 from VisitedCities vc where vc.Country = mc.Country and vc.City = mc.City)