Msg 8672, Level 16, State 1, Line 1 The MERGE operation tried to UPDATE or DELETE the same line more than once

Question

I try to update / insert ... I get an error: (Msg 8672, Level 16, State 1, Line 1 The MERGE statement tried to UPDATE or DELETE the same line more than once. This happens when the target line matches more than one source row. The MERGE statement cannot update / delete the same row of the target table several times. Refine the ON clause to ensure that the 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()); 

Answer 1

This happens when the target line matches more than one source line.
The MERGE statement cannot update / delete the same row of the target table several times.

I think the error is pretty clear.

You have duplicate rows in the source table. Thus, for one row with the table Rownumber = X in Rows there is a table with Rowno = X in the Temp_info table containing more than one row.

The SQL server would like to know which row of these duplicate rows in the source table is used to update a single row in the target table.

[Change]

In response to your answer: one option is to duplicate before starting 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 the DOB as a field that defines the order to find out which is the last. Replace this field with the one you want to use for the order.