MERGE error, how to identify row causing error

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

  • 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
  • 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/

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

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