CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008merge
Vikram
Vikram

Reputation: 61

Msg 8672, Level 16, State 1, Line 1 The MERGE statement attempted to UPDATE or DELETE the same row more than once

Am trying to perform a update/insert ... i get the 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. 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. )

Merge into Rows as R
USING (select RowNo,DOB,Pin,State,RowType,RowStatus from Temp_info) as tmp
ON R.Rownumber=tmp.Rowno
WHEN MATCHED THEN 
UPDATE 
    SET R.DOB=tmp.DOB,
    R.Pin=tmp.Pin,
    R.State=tmp.State,
    R.RowType=tmp.RowType,
    R.RowStatus=tmp.RowStatus,
    R.deleted='N',
    R.last_modified=getdate()
WHEN NOT MATCHED THEN 
INSERT (RowNumber,DOB,Pin,State,RowType,RowStatus,deleted,last_modified)
values (tmp.RowNo,tmp.DOB,tmp.Pin,tmp.State,tmp.RowType,tmp.RowStatus,'N',GETDATE());

Upvotes: 6

Views: 9741

Answers (1)

Alex Aza
Alex Aza

Reputation: 78487

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.

The error is pretty self-explanatory, I think.

You have duplicate rows in the source table. So for one row with Rownumber = X in Rows table there are more than one row with Rowno = X in Temp_info table.

SQL server would like to know which row from those duplicate rows in the source table to use for the update on one row in the target table.

[Edit]

In response to your answer: one of the options is to duplicates, before running the merge:

with cte
as
(
    select row_number() over(partition by RowNo order by DOB desc) RowNumber
    from Temp_info
)
delete cte
where RowNumber > 1

I used DOB as the field that defines order to know what is the last. Replace this field with the one that you want to be used for order.

Upvotes: 5

Related Questions