The MERGE statement attempted to UPDATE or DELETE the same row more than once

Question

Post reply

The MERGE statement attempted to UPDATE or DELETE the same row more than once

vipin_jha123

Hall of Fame

Points: 3085
More actions
March 12, 2015 at 12:22 am

#398169

Hi ,
I have created a Dynamic Merge statement SCD2 Store procedure , which insert the records if no matches and if bbxkey matches from source table to destination table thne it updates old record as lateteverion 0 and insert new record with latest version 1.
I am getting below error when I ahve more than 1 bbxkey in my source table.
how can i ignore this.
BBXkey is nothing but I am deriving by combining 2 columns.
Msg 8672, Level 16, State 1, Line 6
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.
Regards,
Vipin Jha

Viewing 5 posts - 1 through 4 (of 4 total)

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

Phil Parkin SSC Guru Points: 246977 More actions · Answer 1

You cannot 'ignore' this. You need to refine your match criteria to avoid it.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

LinksUp SSCertifiable Points: 6526 More actions · Answer 2

vipin_jha123 (3/12/2015)
Hi ,
Msg 8672, Level 16, State 1, Line 6
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.

With the information you have provided, the error message is your best source to tell you what the problem is. You need to craft a better ON clause.

To get help specific to your problem, you will need to post DLL for the table(s), consumable data, expected output and your current query that is producing the error message.

Please read the link in my signature for more information about how to post correctly.

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

SQL.AP Hall of Fame Points: 3244 More actions · Answer 3

Seems your JOIN is returning multiple records for your single KEY.

Check if there are any duplicates or multiple records present for your KEY.

____________________________________________________________

AP

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 4

Based on the Error that you have posted, the problem seems to be in the SOURCE dataset which is producing more than one match.

It could be that you need to refine that dataset or the ON statement as per the earlier suggestion so that you get a one on one match.

Without knowing the data or the datasets its incredibly difficult to do a full diagnostic, especially if there is a significant volume of data.

One thing I will suggest is that you write a query with a GROUP BY and HAVING COUNT(*)>1 on the base dataset, where the GROUP BY is the list of columns in the ON clause, this will help you identify the rows that are giving you a problem.

(Sidebar : any one else wish there was a CELKO filter on the forum? :-P)

_________________________________________________________________________
SSC Guide to Posting and Best Practices