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,

Stricture

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;

Marven

The first one looks promissing. I'll give it a shot and let you know. For the second, I think I'd rather have a separate DELETE query run instead of doing a CTE. The original query is a bit more complicated than the sample above. – Stricture

Took a while to get back to this. The first option on the DELETE condition worked perfect. Thanks, – Stricture

Answer 2

Hope this helps.

--  myTable
--  (
--      GroundID bigint, -- FK
--      GroupID, bigint, -- FK
--      AcceptingReservations bit
--  );

merge into myTable as target
using @tmpTable as source
    on  ( source.GroundID   = target.GroundID )
    and ( source.GroupID    = target.GroupID )
when
    not matched by target
    then
        insert ( GroundID, GroupID, AcceptingReservations )
        values
        (
            source.GroundID,
            source.GroupID,
            source.AcceptingReservations
        )
-- If there is a row that matches, update values;
when matched
    then
        update set
            target.AcceptingReservations = source.AcceptingReservations
-- If they do not match, delete for that GroundID only;
when
    not matched by source
    and target.GroundID = @GroundID
        then
            delete;

Allsun

Can you explain this a bit please? Where is the variable @GroundID declared in the not matched by source clause? – Overdo

Answer 3

Create a table type variable in sql database

CREATE TYPE [dbo].[YourTableType] AS TABLE(
     [AccountID] [int] NULL,
     [ItemID] [int] NULL
     )
   GO

Make Changes in your Update Procedure

ALTER PROCEDURE YourProcedure
@Items YourTableType READONLY
AS
BEGIN
   MERGE INTO [dbo].[YourTable] as Target
   USING @Items as Source
ON 
    Target.[AccountID]=Source.[AccountID] and 
    Target.[ItemID]=Source.[ItemID] 
   WHEN NOT MATCHED by TARGET THEN
     INSERT 
        ([AccountID],
         [ItemID])
     VALUES 
       (Source.[AccountID],
        Source.[ItemID])

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

END

Bidet

Answer 4

The above answer works in the situation that is described.

I have an exception table that I use to store exceptions to invoices. I only want it to contain the current exceptions for the invoice. So, if I fix some of things in the invoice data and run the process again it will create a new list of exceptions. I want it to add the new exceptions, update existing ones, and delete the exceptions that no longer exist - SO LONG AS THEY BELONG TO THE SAME INVOICE (or whatever).

The problem I had was that the MERGE statement WHEN NOT MATCHED BY SOURCE THEN DELETE would delete everything in the TARGET table; not just the extra items no longer in the SOURCE! I could not qualify the WHEN NOT MATCHED BY SOURCE statement so that the DELETE would only affect the same invoice number in the TARGET that was no longer in the SOURCE.

An error told me "Only target columns are allowed in the 'WHEN NOT MATCHED BY SOURCE' clause of a MERGE statement."

So you have to qualify the TARGET rows with a variable.

Mitchelmitchell

Answer 5

I just ran into the same issue mentioned by @NotMe. The second option mentioned by @Martin Smith just worked fine. I think the best option is to create a target CTE to define your target records with all the possible conditions. So while doing all the merge operations, we do not touch any records that are not matching our specific conditions

Psychopathology

This does not provide an answer to the question. Once you have sufficient reputation you will be able to comment on any post; instead, provide answers that don't require clarification from the asker. - From Review – Dunning