MERGE 查询和删除记录

2022-01-16 00:00:00 merge tsql sql-server sql-server-2008-r2

我有一张看起来像这样的表格:

I have a table that looks something like:

AccountID, ItemID
1, 100
1, 200
2, 300

我有一个接受表值参数的过程，该参数更新与帐户关联的项目.我们将传递如下内容:

I have a proc that accepts a table value parameter which updates the Items associated with an account. We'll pass something like the following:

AccountID, ItemID
3, 100
3, 200

过程看起来像:

procedure dbo.MyProc( @Items as dbo.ItemListTVP READONLY )
AS
BEGIN
  MERGE INTO myTable as target
    USING @Items
       on (Items.AccountId = target.AccountId)
       AND (Items.ItemId = target.ItemId)
    WHEN NOT MATCHED BY TARGET THEN
        INSERT (AccountId, ItemId)
        VALUES (Items.AccountId, Items.ItemId)

   ;

END

根据传入的数据，我希望它会向表中添加 2 条新记录，它确实如此.

Based on the passed in data I expect it to add 2 new records to the table, which it does.

我想要的是有一个 WHEN NOT MATCHED BY SOURCE 子句，它将删除指定帐户的项目不匹配.

What I want is to have a WHEN NOT MATCHED BY SOURCE clause which will remove items for the specified account that aren't matched.

例如，如果我通过了

AccountID, ItemID
1, 100
1, 400

然后我希望它删除具有 1, 200 的记录；但留下所有其他人.

Then I want it to delete the record having 1, 200; but leave ALL of the others.

如果我这样做:

WHEN NOT MATCHED BY SOURCE THEN
  DELETE;

然后它将删除未引用帐户的所有记录(即:帐户 ID 2 和 3).

then it will remove all records for accounts not referenced (ie: account ids 2 and 3).

我该怎么做?

谢谢，

推荐答案

我可以想到两种明显的方法，但它们都涉及再次处理 TVP.

I can think of two obvious ways but both of them involve processing the TVP again.

首先是简单地改变DELETE条件

    WHEN NOT MATCHED BY SOURCE 
    AND target.AccountId IN(SELECT AccountId FROM @Items) THEN
        DELETE;

第二种是使用CTE来限制目标

The second is to use a CTE to restrict the target

WITH cte as
(
SELECT ItemId, AccountId 
FROM @myTable m
WHERE EXISTS 
  (SELECT * FROM @Items i WHERE i.AccountId = m.AccountId)
)
      MERGE INTO cte as target
        USING @Items Items
           ON (Items.AccountId = target.AccountId) AND
              (Items.ItemId = target.ItemId)
        WHEN NOT MATCHED BY TARGET THEN
            INSERT (AccountId, ItemId)
            VALUES (Items.AccountId, Items.ItemId)
         WHEN NOT MATCHED BY SOURCE THEN 
            DELETE;

相关文章