sql-server - MERGE 查询和删除记录

Question

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

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

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

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 条新记录，它确实做到了。

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

例如，如果我通过了

AccountID, ItemID
1, 100
1, 400

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

如果我这样做:

WHEN NOT MATCHED BY SOURCE THEN
  DELETE;

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

我该怎么做？

谢谢

Answer 1

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

第一个是简单地更改DELETE条件

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

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

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;