SQL Server error 8672: MERGE touched the same row twice
In a MERGE, one target row matched more than one source row, so it would be updated or deleted twice. Make the source unique on the join key.
- Error number
8672- Severity
- 16
- Category
- Syntax and compilation
What it means
MERGE requires that each target row match at most one source row in WHEN MATCHED actions. If the ON condition lets two source rows match the same target row, SQL Server can't decide which update to apply and fails with 8672.
Common causes
- Duplicate keys in the source table or staging data.
- An
ONclause that doesn't cover the full key, such as joining on customer only when the key is customer plus date.
How to fix it
-
Find the duplicate keys in the source.
SELECT CustomerId, COUNT(*) FROM staging.Customers GROUP BY CustomerId HAVING COUNT(*) > 1;
-
Deduplicate the source in a CTE before merging, keeping the row you want.
WITH src AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY UpdatedAt DESC) AS rn FROM staging.Customers ) MERGE dbo.Customers AS t USING (SELECT * FROM src WHERE rn = 1) AS s ON t.CustomerId = s.CustomerId WHEN MATCHED THEN UPDATE SET t.Name = s.Name WHEN NOT MATCHED THEN INSERT (CustomerId, Name) VALUES (s.CustomerId, s.Name); -
Make the
ONcondition match the full key.
Related errors
2627Violation of PRIMARY KEY or UNIQUE KEY512Subquery returned more than 1 value21000PostgreSQL: more than one row returned by a subquery
More syntax and compilation errors
102Incorrect syntax near156Incorrect syntax near the keyword105Unclosed quotation mark after the string4145Non-boolean expression in a condition8120Column not in GROUP BY or aggregate8118Aggregate without GROUP BY1934SET options have incorrect settings
The code and message text are from the Microsoft Database Engine events and errors reference. Browse the full SQL Server error list or search every database error.
Work on SQL Server in SQLly
A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for SQL Server and the other databases you use.