Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Resolving the MERGE statement attempted to UPDATE or DELETE the same row more than once error


By:   |   Read Comments (3)   |   Related Tips: 1 | 2 | 3 | 4 | 5 | 6 | More > T-SQL

Attend a SQL Server Conference for FREE >> click to learn more


Problem

I'm receiving the following error when attempting to run a T-SQL MERGE statement: "Msg 8672, Level 16, State 1, Line 123. 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."

Error message in SSMS

How can I resolve this error?

Solution

In my opinion, the MERGE statement is one of the hardest statements in T-SQL to master due to its versatility and complexity. Fortunately, this error is easy to resolve. In our example shown below, we have a staging table which is will serve as our source.

Source table contents

We also have a type two slowly changing dimension table which is our target.

Target table contents

Notice that in both the source and target tables we have two source system ID columns.

The error message directs us to modify the ON clause. Examining the ON clause below, we see that we are only matching on one of the two source system keys. This is the cause of our error because the merge statement is trying to update both of the rows in the table shown above.

ON clause that is causing the error

We fix the ON clause to match on both of the two source system keys.

ON clause fixed

Now, when we execute the T-SQL MERGE statement, it runs successfully and inserts our new values and updates the old values in the slowly changing dimension table.

Correct MERGE statement output

The complete T-SQL MERGE statement is shown in the box below.

-- begin of insert using merge
insert into dbo.tblDimSCDType2Example
( --Table and columns in which to insert the data
  SourceID1,
  SourceID2,
  Attribute1,
  Attribute2,
  Check_Sum,
  EffectiveDate,
  EndDate
)
-- Select the rows/columns to insert that are output from this merge statement 
-- In this example, the rows to be inserted are the rows that have changed (UPDATE).
select    
SourceID1,
SourceID2,
Attribute1,
Attribute2,
Check_Sum,
EffectiveDate,
EndDate
from
(
  -- This is the beginning of the merge statement.
  -- The target must be defined, in this example it is our slowly changing
  -- dimension table
  MERGE into dbo.tblDimSCDType2Example AS target
  -- The source must be defined with the USING clause
  USING 
  (
    -- The source is made up of the attribute columns from the staging table.
    SELECT 
    SourceID1,
    SourceID2,
    Attribute1,
    Attribute2,
    Check_Sum
    from dbo.tblStaging
  ) AS source 
  ( 
    SourceID1,
    SourceID2,
    Attribute1,
    Attribute2,
    Check_Sum
  ) ON --We are matching on SourceID1 and SourceID2 in the target table and the source table.
  (
    target.SourceID1 = source.SourceID1
    and target.SourceID2 = source.SourceID2
  )
  -- If the ID's match but the CheckSums are different, then the record has changed;
  -- therefore, update the existing record in the target, end dating the record 
  -- and set the CurrentRecord flag to N
  WHEN MATCHED and target.Check_Sum <> source.Check_Sum 
               and target.CurrentRecord='Y'
  THEN 
  UPDATE SET 
    EndDate=getdate()-1, 
    CurrentRecord='N', 
    LastUpdated=getdate(), 
    UpdatedBy=suser_sname()
  -- If the ID's do not match, then the record is new;
  -- therefore, insert the new record into the target using the values from the source.
  WHEN NOT MATCHED THEN  
  INSERT 
  (
    SourceID1, 
    SourceID2,
    Attribute1,
    Attribute2,
    Check_Sum
  )
  VALUES 
  (
    source.SourceID1, 
    source.SourceID2,
    source.Attribute1,
    source.Attribute2,
    source.Check_Sum
  )
  OUTPUT $action, 
    source.SourceID1, 
    source.SourceID2,
    source.Attribute1,
    source.Attribute2,
    source.Check_Sum,
    getdate(),
    '12/31/9999'
) -- the end of the merge statement
--The changes output below are the records that have changed and will need
--to be inserted into the slowly changing dimension.
as changes 
(
  action, 
  SourceID1, 
  SourceID2,
  Attribute1,
  Attribute2,
  Check_Sum,
  EffectiveDate,
  EndDate
)
where action='UPDATE';
Next Steps

Check out more tips and tutorials about T-SQL on MSSQLTips.com:



Last Update:


signup button

next tip button



About the author





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Monday, June 26, 2017 - 10:03:06 AM - Abhishek Kulkarni Back To Top

I am doing and incremental load using CDC. I am getting the above error on merge query. But There are no such source ID tables. how should I merge. There are two columns - DATAAREAID AND SALESID which are unique (PK) and I am using them on the ON clause.

Any help Appretiated. 

 


Thursday, April 28, 2016 - 9:31:42 AM - Sourav Kings Back To Top

Great explanantion and insight. The logic worked perfectly.

You saved my day Dr. Snider. Cheers :)

 

Sourav


Thursday, September 10, 2015 - 3:46:06 AM - SVS Sudheer Back To Top

This tip helps me. Thanks


Learn more about SQL Server tools