Home > Back-end >  IF EXISTS and MERGE Statement
IF EXISTS and MERGE Statement

Time:11-24

I have data flowing into one table from multiple other tables lets say: Table_A Then I have a Merge stored proc that takes data from table A merges it with Table B.

However, something doesn't seem to be right. If i truncate and load the data it works fine, but if i dont truncate and load, and just fetch the query by eachh hour I get the error message saying

Msg 8672, Level 16, State 1, Procedure Merge_Table_A, Line 4 [Batch Start Line 0] The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.

How can I overcome this?

I want to be able to incrementally load the data and not do truncate loads, but at the same time have a stored proc that updates or inserts or doesnt care if the row already exists.

CodePudding user response:

Seems you have duplicate rows in your target table which are loaded from your previous runs.

Note: Matching in a Merge does not consider the rows inserted (even duplicate) while running the Merge itself.

Below is my repro example with a sample data:

Table1: Initial data

enter image description here

Table2: Taget table

enter image description here

Merge Statement:

MERGE tb2 AS Target
USING tb1   AS Source
 ON Source.firstname = Target.firstname and 
    Source.lastname = Target.lastname 

-- For Inserts
WHEN NOT MATCHED BY Target THEN
INSERT (firstname, lastname, updated_date) 
VALUES (Source.firstname, Source.lastname, source.updated_date)

-- For Updates
WHEN MATCHED THEN UPDATE SET
   Target.updated_date      = Source.updated_date

-- For Deletes
WHEN NOT MATCHED BY Source THEN
DELETE;

When Merge is executed, it inserts all data without any errors.

enter image description here

New data in tb1:

enter image description here

When I run the Merge statement, it gives me the same error as you

enter image description here

As a workaround using one of the below options,

  1. Add additional conditions if possible in the ON clause to uniquely identify the data.

  2. Remove the duplicates from the source and merge the data into tb2 as below.

     --temp table
     drop table if exists #tb1;
    
     select *  into #tb1 from (
         select *, row_number() over(partition by firstname, lastname order by firstname, lastname, updated_date desc) as rn from tb1) a
     where rn = 1 
    
     MERGE tb2 AS Target
     USING #tb1 AS Source
      ON Source.firstname = Target.firstname and 
         Source.lastname = Target.lastname 
    
     -- For Inserts
     WHEN NOT MATCHED BY Target THEN
     INSERT (firstname, lastname, updated_date) 
       VALUES (Source.firstname, Source.lastname, source.updated_date)
    
     -- For Updates
     WHEN MATCHED THEN UPDATE SET
        Target.updated_date     = Source.updated_date
    
     -- For Deletes
     WHEN NOT MATCHED BY Source THEN
       DELETE;
    

Data merged into tb2 successfully.

enter image description here

  • Related