消息8672，级别16，状态1，行1 MERGE语句尝试多次更新或删除同一行 [英] Msg 8672, Level 16, State 1, Line 1 The MERGE statement attempted to UPDATE or DELETE the same row more than once

问题描述

我正在尝试执行更新/插入...我得到了错误:(Msg 8672，Level 16，State 1，Line 1 MERGE语句尝试多次更新或删除同一行.当目标行与多个源行匹配时，就会发生这种情况. MERGE语句不能多次更新/删除目标表的同一行.优化ON子句以确保目标行最多匹配一个源行，或使用GROUP BY子句对源行进行分组. )

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());

推荐答案

当目标行与多个源行匹配时，就会发生这种情况.
MERGE语句不能多次更新/删除目标表的同一行.

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.

您在源表中有重复的行.因此，对于Rows表中具有Rownumber = X的一行，在Temp_info表中具有多于Rowno = X的一行.

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服务器想知道源表中那些重复行中的哪一行用于目标表中一行的更新.

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.

针对您的回答:一种选择是在运行合并之前复制副本:

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

我使用DOB作为定义顺序的字段，以了解最后一个.将该字段替换为您要用于订购的字段.

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.

这篇关于消息8672，级别16，状态1，行1 MERGE语句尝试多次更新或删除同一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！