SQL Server: how to delete rows with matching ID that were not affected by MERGE?
You can use the WHEN NOT MATCHED BY SOURCE THEN DELETE
clause to achieve this. However, do not just do the following:
MERGE tablename trg
USING (VALUES ('A','B','C','D'),
('A','C','E','F'),
('A','E','K','F'),
('A','F','L','M')) src(keycol1, keycol2, col1, col2)
ON trg.keycol = src.keycol AND trg.keycol2 = src.keycol2
WHEN MATCHED THEN
UPDATE SET col1 = src.col1, col2 = src.col2
WHEN NOT MATCHED BY TARGET THEN
INSERT(keycol1, keycol2, col1, col2)
VALUES(src.keycol1, src.keycol2, src.col1, src.col2)
WHEN NOT MATCHED BY SOURCE THEN
DELETE;
This is something that trips up a lot of people: when using WHEN NOT MATCHED BY SOURCE THEN DELETE
in a MERGE
, you need to be aware that all non-matching rows will be deleted. Therefore, if you only want to delete a subset of the rows in the target table, you must pre-filter the target, either with a view or a CTE. For example:
WITH trg AS (
SELECT *
FROM tablename
WHERE keycol = 'A'
)
MERGE tablename trg
USING (VALUES ('A','B','C','D'),
('A','C','E','F'),
('A','E','K','F'),
('A','F','L','M')) src(keycol1, keycol2, col1, col2)
ON trg.keycol = src.keycol AND trg.keycol2 = src.keycol2
WHEN MATCHED THEN
UPDATE SET col1 = src.col1, col2 = src.col2
WHEN NOT MATCHED BY TARGET THEN
INSERT(keycol1, keycol2, col1, col2)
VALUES(src.keycol1, src.keycol2, src.col1, src.col2)
WHEN NOT MATCHED BY SOURCE THEN
DELETE;
Regarding issues with your IDENTITY
column, I suggest you make it a bigint
to give you more scope.