消息 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 1MERGE 语句多次尝试更新或删除同一行.当目标行匹配多个源行时会发生这种情况.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 Server 想知道源表中那些重复行中的哪一行用于更新目标表中的一行.

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屋！