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.”
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.
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';
Next Steps
Check out more tips and tutorials about T-SQL on MSSQLTips.com:
