MERGE Query and deleting records

Question

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 ``` The proc looks something like: ``` 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 ``` Based on the passed in data I expect it to add 2 new records to the table, which it does. 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. For example, if I pass ``` AccountID, ItemID 1, 100 1, 400 ``` Then I want it to delete the record having 1, 200; but leave ALL of the others. If I just do: ``` WHEN NOT MATCHED…

Accepted Answer

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

The first is simply to change the `DELETE` condition

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

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

MERGE Query and deleting records

Solution

Problem