KB Issue

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.

Issue Details

When updating data across multiple tables, you might encounter this error if duplicate entries exist.

SQL Merge Statement Error
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.

Recreate Issue

This error will happen if you have a SQL Script that joins tables together based on either a Number or Words, in which the tables have multiple instances of the value, when they are only supposed to have one instance.

Resolve Issue

Run the following Script on each table to identify all duplicate values. Then run an update Script to correct the entries, and a delete Script to remove all dead entries ONLY after you have updated the original value with the duplicate values' content.

[SQK Server - Find Duplicates]

CFFCS | CarrzSynEdit: | SQL Script

-- To find duplicate values in a table.

SELECT MemberName,COUNT(*)
FROM Members
GROUP BY MemberName
HAVING COUNT(*) > 1

Example of records found.

126	Darrell Roberts
533	Darrell Roberts

In the live data example above. We checked and found that record 126 contains all the data, while record 533 contains only a single record.
We then updated the 126 to retrieve the 533 value across all relevant tables.

In this example, we are showing only one table to update and two tables to delete.

[SQK Server - Update Statement]

CFFCS | CarrzSynEdit: | SQL Script

-- To update the values

Update AnotherTable set BMID = 126 where BMID = 533

[SQK Server - Delete Statement]

CFFCS | CarrzSynEdit: | SQL Script

Delete from Members where BMID = 533
Delete from AnotherTable where BMID = 533

Fix Your Code

You will need to find out why duplicate records are created. This is the root cause: if you are creating a table set that uses this type of code, all records must be unique.
Review your initial database insert code and troubleshoot the issue causing duplicate records to be inserted.

Review this KB Article to further resolve this issue.

Other Articles Related to this Entry.The MERGE statement attempted to UPDATE or DELETE the same row more than once.«