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;