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 BY SOURCE THEN   DELETE; 

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

How can I do this?

Thanks,

Answer 1

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;