MERGE error, how to identify row causing error

Question

MERGE error, how to identify row causing error

JJR333

Default port

Points: 1469
More actions
November 30, 2017 at 6:26 am

#341042

When I run my storedprocedure to MERGE one table into another I get the following error message.
How do identify the offending row(s) without manually comparing the tables?
Cheers,
Julian
DECLARE @rc int
EXECUTE @rc = [xxx].[dbo].[spIMPORTBewoners]
GO
(1391 row(s) affected)
FIRST MERGE
Msg 8672, Level 16, State 1, Procedure spIMPORTBewoners, Line 454
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.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

JJR333 - Thursday, November 30, 2017 6:26 AM
When I run my storedprocedure to MERGE one table into another I get the following error message.
How do identify the offending row(s) without manually comparing the tables?
Cheers,
Julian
DECLARE @rc int
EXECUTE @rc = [xxx].[dbo].[spIMPORTBewoners]
GO
(1391 row(s) affected)
FIRST MERGE
Msg 8672, Level 16, State 1, Procedure spIMPORTBewoners, Line 454
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.

Without seeing (at least) the merge, there's no way someone can give you an advice

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

LinksUp SSCertifiable Points: 6526 More actions · Answer 2

JJR333 - Thursday, November 30, 2017 6:26 AM
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.

[/quote]
The error message told you the problem and the solution! Your current ON clause can get the same row twice because they are not unique enough to the target table. You need to refine the ON clause.
Beyond that, you need to post DDL, the merge, etc as Luis suggested.

__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/