合并查询和删除记录 [英] MERGE Query and deleting records

问题描述

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

I have a table that looks something like:

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

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

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;

这篇关于合并查询和删除记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！