MERGE Error UPDATE or DELETE Same Row More Than Once

When running a MERGE statement I have received the error:

Msg 8672, Level 16, State 1, Line 1
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.
” several times and am always forced to run back through the query logic to see what has occurred.  Reading the message it is fairly self explanatory as to what has happened.  A row in the target table will be affected more than once from within the MERGE statement which causes the query to fail.

To demonstrate this review the T-SQL code below that will create two tables and populate them with several rows:

USE tempdb;

CREATE TABLE #employeetarget(
FirstName  VARCHAR(50),
MiddleName VARCHAR(50),
LastName   VARCHAR(50),
JobTitle   VARCHAR(25));

CREATE TABLE #employeesource(
FirstName  VARCHAR(50),
MiddleName VARCHAR(50),
LastName   VARCHAR(50),
JobTitle   VARCHAR(25));

INSERT #employeetarget
VALUES(‘David’, ‘M’, ‘Dye’, ‘Da Boss’),
     (
‘John’, ‘A’, ‘Doe’, ‘Nobody’),
     (
‘Mike’, ‘B’, ‘Smith’, ‘Lazy’)

INSERT #employeesource
VALUES(‘David’, ‘M’, ‘Dye’, ‘Da Boss’),
     (
‘David’, ‘M’, ‘Dye’, ‘Unemployed’),
     (
‘John’, ‘A’, ‘Doe’, ‘Nobody’),
     (
‘Mike’, ‘B’, ‘Smith’, ‘Lazy’)

 

The #employeetarget table will need to be updated based on the records in the #employeesource table.  If the FirstName, MiddleName, and LastName of the employee are the same then the JobTitle in the target should be updated to the JobTitle in the source.  If the FirstName, MiddleName, and LastName do not match then it is a new employee and should be INSERTed into the target.  The below MERGE statement will accomplish this, but will also raise the aforementioned error:


MERGE
#employeetarget AS t
USING
#employeesource AS s
ON t.FirstName = s.FirstName
AND t.MiddleName = s.MiddleName
AND t.LastName = s.LastName
WHEN MATCHED THEN
UPDATE SET
 t.JobTitle = s.JobTitle
WHEN NOT MATCHED THEN
INSERT
VALUES
(s.FirstName, s.MiddleName, s.LastName, s.JobTitle);

To understand the error review the results of joining the target and source table together using the same criteria that is used in the MERGE:

SELECT t.*,
             
s.JobTitle
FROM #employeesource s JOIN #employeetarget t
ON t.FirstName = s.FirstName
AND t.MiddleName = s.MiddleName
AND t.LastName = s.LastName

image 

Despite there being only one row in the target for David M Dye the join criteria would result in that row being updated twice. To make matters more confusing breaking the logic out into a separate UPDATE statement and executing that alone succeeds, however querying the target and source again show that no change was made:

 UPDATE #employeetarget
SET JobTitle = s.JobTitle
FROM #employeetarget t JOIN #employeesource AS s
ON t.FirstName = s.FirstName
AND t.MiddleName = s.MiddleName
AND t.LastName = s.LastName

SELECT *
FROM #employeetarget t

image

Despite the statement running successfully the JobTitle remains the same?  The answer is yes the JobTitle was updated and this is also verified in the messages tab:

image

Neither temporary table has defined ordering with a primary key or clustered index so both tables are a heap.  Creating a clustered index on the JobTitle column and re-running the query returns different results:

CREATE CLUSTERED INDEX clx_name
ON #employeesource(JobTitle DESC);

UPDATE #employeetarget
SET JobTitle = s.JobTitle
FROM #employeetarget t JOIN #employeesource AS s
ON t.FirstName = s.FirstName
AND t.MiddleName = s.MiddleName
AND t.LastName = s.LastName

SELECT *
FROM #employeetarget t

image

 

Despite the ambiguity of the UPDATE and the join criteria the statement completes successfully, while the MERGE fails, but the resultant change of the UPDATE is inconsistent and can be partially based on the table ordering, the only way to insure ordering is to use ORDER BY.

So how can a MERGE statement be changed to insure that a record will only be affected once?  The example that I have given is very difficult since there is no flag or date column that shows the most recent record to identify to update the target.  To make this a bit more realistic let’s recreate the target and source tables to include a column, ModDate, that is used to show when a record was modified which will in turn be used to update only the most recent record.

–Create tables with a modified date
USE tempdb;

DROP TABLE #employeetarget;
GO

DROP TABLE #employeesource;
GO

CREATE TABLE #employeetarget(
FirstName  VARCHAR(50),
MiddleName VARCHAR(50),
LastName   VARCHAR(50),
JobTitle   VARCHAR(25),
ModDate        DATE);

CREATE TABLE #employeesource(
FirstName  VARCHAR(50),
MiddleName VARCHAR(50),
LastName   VARCHAR(50),
JobTitle   VARCHAR(25),
ModDate        DATE);

INSERT #employeetarget
VALUES(‘David’, ‘M’, ‘Dye’, ‘Da Boss’, ‘1/1/2012’),
     (
‘John’, ‘A’, ‘Doe’, ‘Nobody’, ‘1/1/2012’),
     (
‘Mike’, ‘B’, ‘Smith’, ‘Lazy’, ‘1/1/2012’);

INSERT #employeesource
VALUES(‘David’, ‘M’, ‘Dye’, ‘Da Boss’, ‘1/1/2012’),
     (
‘David’, ‘M’, ‘Dye’, ‘Unemployed’, ‘1/1/2013’),
     (
‘John’, ‘A’, ‘Doe’, ‘Nobody’, ‘1/1/2012’),
     (
‘Mike’, ‘B’, ‘Smith’, ‘Lazy’, ‘1/1/2012’);
GO

Identifying the most recent record can be done using ROW_NUMBER():

SELECT ROW_NUMBER() OVER(PARTITION BY FirstName, MiddleName, LastName ORDER BY ModDate DESC) AS RowNum,
     
FirstName,
     
MiddleName,
     
LastName,
     
JobTitle,
     
ModDate
FROM #employeesource

image

The above query can be incorporated into a common table expression that is used in the MERGE statement to only return the source rows where the RowNum = 1

WITH cteJobTitle
AS(
SELECT ROW_NUMBER() OVER(PARTITION BY FirstName, MiddleName, LastName ORDER BY ModDate DESC) AS RowNum,
     
FirstName,
     
MiddleName,
     
LastName,
     
JobTitle,
     
ModDate
FROM #employeesource)
–Succeeds
MERGE #employeetarget AS t
USING cteJobTitle
AS s
ON t.FirstName = s.FirstName
AND t.MiddleName = s.MiddleName
AND t.LastName = s.LastName
WHEN MATCHED
AND RowNum = 1
THEN
UPDATE
SET
t.JobTitle = s.JobTitle,
  
t.ModDate = s.ModDate
WHEN NOT MATCHED THEN
INSERT
VALUES
(s.FirstName, s.MiddleName, s.LastName, s.JobTitle, GETDATE());

SELECT*
FROM  #employeetarget

The results show that the JobTitle was updated to the most recent, Unemployed, based on the ROW_NUMBER:

image

Comments ( 5 )

  1. ReplyPrashant Pamani

    Awesome suggestion

  2. ReplyJordy van Eijk

    Thank you very much for explaining what is happening. I also got the above exception on a SP and the solution to fix this with the rownumber is brilliant. Suits my problem.

    • Replysqlsafety

      Glad it helped!!

  3. ReplyEric Simpson

    The above works by dropping all but one update. If all updates are important (eg. asynchronous updates to different columns in the same record) you can use a stored proc to do the updates with an explicit COMMIT in the proc. If the order of the updates is important use a timestamp or row counter to process them in the correct order.

  4. ReplyTawanda Mkutuma

    Please help, how can I solve this error (0 row(s) affected) Msg 50000, Level 16, State 1, Procedure RethrowErrorMessage, Line 35 Error 8672, Level 16, State 1, Procedure LoadArticleBMCCharacteristics, Line 41, Message: 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. (1 row(s) affected) (1 row(s) affected) SQL Code USE [CentralMasterData] GO /****** Object: StoredProcedure [Central].[LoadArticleBMCCharacteristics] Script Date: 10/19/2015 07:13:11 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER procedure [Central].[LoadArticleBMCCharacteristics] (@InsertedCount int OUTPUT, @DeletedCount int OUTPUT, @UpdatedCount int OUTPUT, @ProcessedCount int OUTPUT) as This procedure loads the Lookup table as a Slowly Changing Dimension Type I (i.e. no history is kept). The Procedure returns a count of how many records were affected during the processing. */ BEGIN TRY --Declare @InsertedCount int, @DeletedCount int, @UpdatedCount int, @ProcessedCount int Declare @LoadingDate datetime, @ExpiredDate datetime, @IsFullLoad bit Select @IsFullLoad = dbo.IsFullLoad('SAP') Set @LoadingDate = getdate() Set @ExpiredDate = DATEADD(second, -1, @LoadingDate) -- create a table similar to the Source Table but including a ChangeType Declare @ProcessedRecords TABLE ( ChangeType VARCHAR(20), [bmc] [varchar](20) NOT NULL, [bmc_txt] [varchar](100) NULL, [char] [varchar](30) NOT NULL, [char_txt] [varchar](100) NULL, [prof] [varchar](20) NOT NULL, [prof_txt] [varchar](100) NULL, [MDMBaseMerchCategoryCode] [varchar](20) NULL ) -- Handle Type I Changes ----------------------- MERGE INTO CentralMasterData.Central.ArticleBMCCharacteristics AS lkp USING CentralMasterData.[Source].vwActiveSAPArticleMerchBMCChars scd -- Match On Business Key ON scd.MDMBaseMerchCategoryCode = lkp.BaseMerchCode and scd.[char] = lkp.CharacteristicCode -- Update Existing Records where the columns below have changed WHEN MATCHED -- check for changes AND (lkp.BaseMerchDescription scd.bmc_txt or lkp.CharacteristicDescription scd.char_txt or lkp.ProfileDescription scd.prof_txt or lkp.ProfileCode scd.prof or lkp.DateLoaded scd.DateLoaded ) THEN Update Set lkp.BaseMerchDescription = scd.bmc_txt , lkp.CharacteristicDescription = scd.char_txt , lkp.ProfileDescription = scd.prof_txt , lkp.ProfileCode = scd.prof , lkp.DateLoaded = @LoadingDate , lkp.LastModifiedDate = @LoadingDate -- Handle New Records: Add New Records that we haven't received before WHEN NOT MATCHED THEN INSERT VALUES ( MDMBaseMerchCategoryCode , scd.bmc_txt , [char] , scd.char_txt , scd.prof , scd.prof_txt , @LoadingDate --as DateLoaded , @LoadingDate --as LastModifiedDate ) -- Save Results to a temp table and include the type of action (i.e. Insert, Update) OUTPUT $action as ChangeType , scd.bmc , scd.bmc_txt , scd.[char] , scd.char_txt , scd.prof , scd.prof_txt , scd.MDMBaseMerchCategoryCode INTO @ProcessedRecords; -- Handle Deletes (do this in a seperate command so that we can get a count of deleted/archived records) --------------------------------- If @IsFullLoad = 1 Begin MERGE INTO Central.ArticleBMCCharacteristics AS lkp -- compare only to active records (i.e. records that aren't expired) USING Source.vwActiveSAPArticleMerchBMCChars scd -- Match On Business Key ON scd.MDMBaseMerchCategoryCode = lkp.BaseMerchCode and scd.[char] = lkp.CharacteristicCode -- Handle Deleted/Archived Records: Expire Records that no longer exist WHEN NOT MATCHED BY SOURCE THEN DELETE; Select @DeletedCount = @@Rowcount End Else Begin -- If this is a delta load (changes only) then only expire records that have explicitly been specified as deleted or archived -- Handle Deleted/Archived Records: Expire Records that no longer exist Delete Central.ArticleBMCCharacteristics From Central.ArticleBMCCharacteristics lkp Inner Join SAP.vwDeletedSAPArticleMerchBMCChars del on lkp.BaseMerchCode = del.bmc and lkp.CharacteristicCode = del.[char] Select @DeletedCount = @@Rowcount End Select @InsertedCount = COUNT(*) From @ProcessedRecords where ChangeType = 'INSERT' Select @UpdatedCount = COUNT(*) From @ProcessedRecords where ChangeType = 'UPDATE' Select @ProcessedCount = COUNT(*) From Source.vwActiveSAPArticleMerchBMCChars Select 'Processed', @ProcessedCount Select 'Changed', @UpdatedCount Select 'New', @InsertedCount Select 'Deleted', @DeletedCount END TRY BEGIN CATCH -- Rethrow the error to the calling program EXECUTE [Admin].RethrowErrorMessage END CATCH GO

Leave a Reply


Hit Counter provided by Sign Holders