Resolving the MERGE statement attempted to UPDATE or DELETE the same row more than once error
By: Dallas Snider | Comments (7) | Related: 1 | 2 | 3 | 4 | 5 | 6 | More > TSQL
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."
How can I resolve this error?
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.
We also have a type two slowly changing dimension table which is our target.
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.
We fix the ON clause to match on both of the two source system keys.
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.
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';
Check out more tips and tutorials about T-SQL on MSSQLTips.com:
- Using the SQL Server MERGE Statement to Process Type 2 Slowly Changing Dimensions
- Advice for Learning T-SQL SELECT Statement Step By Step
- SQL Server INSERT Command Tutorial
- SELECT command for SQL Server Tutorial
About the author
View all my tips