Saturday, December 27, 2008

SQL Server 2008 Merge Statement Error

If multiple rows match in the target for the criteria then MERGE statement will throw the following error

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

MERGE statement is deterministic whereas update with join will update any row without error. You can use one of the following options to fix the error

1.Make sure that the target row matches at most one source row
2.Use a GROUP BY to group the source rows.

No comments: