Home Forums SQL Server 2017 SQL Server 2017 - Development Delete rows based on a second table RE: Delete rows based on a second table
February 19, 2018 at 10:17 am
ThomasRushton - Monday, February 19, 2018 8:25 AMIt sounds as though you want the "DELETE FROM x JOIN y" type of query shown in the example D of the DELETE syntax - https://docs.microsoft.com/en-us/sql/t-sql/statements/delete-transact-sql#d-using-joins-and-subqueries-to-data-in-one-table-to-delete-rows-in-another-tableFor example:
--table variables because lazy
DECLARE @MasterCities TABLE (Country varchar(20), city varchar(20))
DECLARE @VisitedCities table (Country varchar(20), city varchar(20))--dummy data
INSERT INTO @MasterCities values('UK', 'London'), ('UK', 'Birmingham'), ('UK','Edinburgh'), ('UK','Belfast'), ('Canada', 'London')
INSERT INTO @VisitedCities VALUES ('UK', 'London'), ('UK', 'Edinburgh')--see what we've got so far
SELECT * FROM @MasterCities
SELECT * FROM @VisitedCities--delete matching records from Master table
DELETE m
FROM
@MasterCities AS m
INNER JOIN @VisitedCities AS v ON v.Country = m.Country
AND v.city = m.city;--check the results
SELECT * FROM @MasterCities
SELECT * FROM @VisitedCities
Many thanks