SQL Server: how to delete rows with matching ID that were not affected by MERGE?

Question

I have a table with an auto incrementing id and a unique constraint across two columns, keycol1 and keycol2.

Suppose the table has this data:

H| (id, keycol1, keycol2, col1, col2)
 | (1, 'A',     'B',     'A',  'E' )
 | (2, 'A',     'C',     'J',  'K' )
 | (3, 'A',     'D',     'H',  'I' )

I then use a MERGE statement to update all the records corresponding to keycol1:

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 THEN
    INSERT (keycol1, keycol2, col1, col2)
    VALUES (src.keycol1, src.keycol2, src.col1, src.col2);

This works great to update existing records or add additional records as needed. In our example, rows 1 and 2 will be updated, and rows 4 and 5 will be inserted.

How can I modify the query to delete any rows in tablename where keycol1 = 'A' that were not affected by the MERGE statement? In our example, row 3 should be deleted.

Prior to trying merge, I attempted to solve this by using a DELETE followed by INSERTing multiple values in one transaction. This had the effect of auto incrementing the ID column each time. Since the merge operation would be frequent and there is a lot more than 3 rows involved in each transaction, I'm trying to find something more efficient and avoid the risk of overflowing the id column.

If you have rows already inserted (and hence already taken identity values), and you delete those rows from the merge, how is this going to prevent using identity values? Having dealt with a lot of tables where we ran out of identity and had to update later, this is intrusive and tedious, just make it bigint now and never think about it again. For transaction safety, please, please, please put a HOLDLOCK hint on the target of the merge, but more generally, please read all the links here: [So, you want to use MERGE, eh?](https://sqlblog.org/merge) — Aaron Bertrand, Jan 23 '22 at 05:03

score 6 · Answer 1 · answered Jan 23 '22 at 11:58

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.

Moulitharan M · Answer 2 · 2022-01-23T07:48:29.030

Try adding another column delete_flag to your table

Before running the merge block, update all the records delete_flag to 0.

In the merge block, for both insert and update make the value as 1 for the delete_flag column.

Once merge is complete, delete the records that have delete_flag as 0.

update tablename set delete_flag = 0

MERGE tablename trg
USING (VALUES ('A','B','C','D',1),
              ('A','C','E','F',1),
              ('A','E','K','F',1),
              ('A','F','L','M',1)) src(keycol1, keycol2, col1, col2,delete_flag)
  ON trg.keycol = src.keycol AND trg.keycol2 = src.keycol2
WHEN MATCHED THEN
   UPDATE SET col1 = src.col1, col2 = src.col2, delete_flag = 1
WHEN NOT MATCHED THEN
   INSERT(keycol1, keycol2, col1, col2,delete_flag)
   VALUES(src.keycol1, src.keycol2, src.col1, src.col2,1);

delete from tablename where delete_flag = 1

Hope this solves your problem, happy to discuss it further.

score -1 · Answer 3 · answered Jun 22 '23 at 14:16

-1

delete from table1 a where not exists ( select 1 from table2 b where a.xxx = b.xxx and a.yyy = b.yyy and a.zzz = b.zzz);

answered Jun 22 '23 at 14:16

marian

1

Thank you for contributing to the Stack Overflow community. This may be a correct answer, but it’d be really useful to provide additional explanation of your code so developers can understand your reasoning. This is especially useful for new developers who aren’t as familiar with the syntax or struggling to understand the concepts. **Would you kindly [edit] your answer to include additional details for the benefit of the community?** – Jeremy Caney Jun 24 '23 at 00:23

SQL Server: how to delete rows with matching ID that were not affected by MERGE?

3 Answers3