SQL SERVER – FIX : Error Msg 8672 – The MERGE Statement Attempted to UPDATE or DELETE the Same Row More Than Once

SQL SERVER - FIX : Error Msg 8672 - The MERGE Statement Attempted to UPDATE or DELETE the Same Row More Than Once error While I have been blogging about SQL Server for a long time, I still believe that there is still a lot of error which are not discovered by me. It is always fun to see new error and find a fix for the issue. Let us see how to fix error related to MERGE statement.

Here is one of the errors which I encountered while preparing for a demo.

Msg 8672, Level 16, State 1, Line 19
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.

While the error message was telling me the issue, I wanted to know how to avoid it. Here is the sample code which anyone can use and reproduce the same error.

SET NOCOUNT ON
GO
USE tempdb
GO
CREATE TABLE Employee(EmpID INTEGER,EmpName VARCHAR(15) )
GO
INSERT INTO Employee VALUES(1,'Pinal')
INSERT INTO Employee VALUES(2,'SQLServer')
INSERT INTO Employee VALUES(1,'SQLAuthority')
GO 
CREATE TABLE EmployeeSalary (EmpID INTEGER ,Salary INTEGER)
GO
INSERT INTO EmployeeSalary VALUES(1,235)
INSERT INTO EmployeeSalary VALUES(2,255)
GO
MERGE EmployeeSalary AS es
 USING (SELECT EmpID,EmpName FROM Employee) AS m
ON es.EmpID = m.EmpID
WHEN MATCHED THEN
update SET es.Salary = es.Salary + 25;

Looking at the error message, it is clear that the merge statement is trying to update the target table Employee Salary but while doing so, SQL server encountered duplicate entry in the source table (defined as a query)

Hence, here in the table Employee which is the source table in Merge statement, there are two similar entries for EmpID (1) . This tries to update the target the table Employee Salary which has one unique record for this EmpID. Hence this operation is not allowed in Merge and it throws the above error when duplicate entry is seen in the source table (column ref in ON clause in Merge).

WORKAROUND / SOLUTION

To Resolve this issue, we need to remove the duplicate data from the source entities or to prevent such errors from being triggered. We can also create primary key constraints on fields referred in the ON clause of the Merge statement.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL Error Messages, SQL Scripts, SQL Server
Previous Post
SQL SERVER – PRINTing Status Using RAISERROR With NOWAIT Option
Next Post
SQL SERVER – Errorlog The Server was Unable to Initialize Encryption Because of a Problem with a Security Library

Related Posts

5 Comments. Leave new

  • THANKS FOR THE SHARING THIS INFORMATION

    Reply
  • Hi, Dave ;)

    I think that you are right about removing duplicate keys from DB, but changing the code to update will make it work to:

    SET NOCOUNT ON
    GO
    USE tempdb
    GO
    CREATE TABLE Employee(EmpID INTEGER,EmpName VARCHAR(15) )
    GO
    INSERT INTO Employee VALUES(1,’Pinal’)
    INSERT INTO Employee VALUES(2,’SQLServer’)
    INSERT INTO Employee VALUES(1,’SQLAuthority’)
    GO
    CREATE TABLE EmployeeSalary (EmpID INTEGER ,Salary INTEGER)
    GO
    INSERT INTO EmployeeSalary VALUES(1,235)
    INSERT INTO EmployeeSalary VALUES(2,255)
    GO

    UPDATE es
    SET es.Salary = es.Salary + 25
    FROM EmployeeSalary AS es
    JOIN (SELECT EmpID,EmpName FROM Employee) AS m
    ON es.EmpID = m.EmpID

    Thanks, Cezary

    Reply
  • Hi Dave.. nice post. I am in a situation where source will have multiple records for same primary key , with different actions like insert , then update (an extra column on source “action” represents insert/update/delete). In this case I would like to insert the record first and then update. However merge is not allowing it to happen with the error you mentioned. Unfortunately I cannot pick only one record from source, as I need to port all source data into destination. Any suggestions on this? Can we still use Merge to get through this error in any way?

    Reply

Leave a Reply

Menu
Exit mobile version