// SQL Server error reference

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
Error 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.

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 ON clause that doesn't cover the full key, such as joining on customer only when the key is customer plus date.

How to fix it

  1. Find the duplicate keys in the source.

    SELECT CustomerId, COUNT(*) FROM staging.Customers GROUP BY CustomerId HAVING COUNT(*) > 1;
  2. 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);
  3. Make the ON condition match the full key.

Related errors

More syntax and compilation errors

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.

// fewer errors, faster fixes

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.