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

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)