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

yx2lnoni 于 12个月前发布在其他

关注(0)|答案(3)|浏览(116)

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.

sql-server

来源：https://stackoverflow.com/questions/70819182/how-to-delete-rows-with-matching-id-that-were-not-affected-by-merge

3条答案

按热度按时间

wlzqhblo1#

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.

赞(0）回复(0）举报 12个月前

gcxthw6b2#

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.

赞(0）回复(0）举报 12个月前

lymgl2op3#

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);

赞(0）回复(0）举报 12个月前