SQL Server MERGE语句的问题

Source Table

源表

Id, Name, Address
1   A     #202
1   A     #203
1   A     #204
2   A     #202

Target Table

目标表

Id, Name, Address
1   A     NULL

After Merge

合并后

Id, Name, Address
1   A     #202
2   A     #202

I am using this SQL

我正在使用这个SQL。

create table #S   (ID int, Name varchar(25) NULL, Address varchar(25) NULL)
create table #T   (ID int, Name varchar(25) NULL, Address varchar(25) NULL)

 INSERT #S values(1, 'A', '#202')
 INSERT #S values(1, 'A', '#203')
 INSERT #S values(1, 'A', '#203')
 INSERT #S values(1, 'A', '#204')

 INSERT #T values(1, 'A', NULL)

 MERGE #T USING
  (
Select id, name, address 
from #S
  ) AS S(id,name,address)
 on #T.id=S.id and #T.Name=S.Name
 when not matched THEN
    INSERT values(S.id,S.Name, S.Address)
 when matched then
    update set Address = S.Address;
 GO 

 Select * from #T
 GO 

 Select * from #S
 GO

This causes an error

这将导致一个错误

Msg 8672, Level 16, State 1, Line 18
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.

Msg 8672，第16级，状态1，第18行合并语句试图多次更新或删除同一行。当目标行匹配多个源行时发生这种情况。合并语句不能多次更新/删除目标表的同一行。细化ON子句以确保目标行最多匹配一个源行，或者使用GROUP BY子句对源行进行分组。

I want to update the row in A with Address value from any of the three matching values. How to do this?

我想从三个匹配值中的任意一个更新A中的地址值。如何做到这一点呢?

2 个解决方案

#1

Any of the four values in #S will match your target table's single row value (all values in #S have id = 1 and name = 'A' - so they all match the single row in the target), thus this value will be updated four times - that's what the error says, and it's absolutely right.

任何的四个值在#年代将匹配你的目标表的行值(所有值#年代id = 1和name = ' A ',所以他们都匹配目标中的单行),因此这个值将被更新的四倍——这就是错误的说,这是绝对正确的。

What is it you really want to achieve here??

你想在这里实现什么?

Do you want to set the Address to the first of the values from the source table? Use a TOP 1 clause in your subselect:

要将地址设置为源表中的第一个值吗?在您的子选择中使用TOP 1子句:

MERGE #T 
USING (SELECT TOP 1 id, name, address FROM #S) AS S
ON #T.id = S.id AND #T.Name = S.Name
WHEN NOT MATCHED THEN
    INSERT VALUES(S.id,S.Name, S.Address)
WHEN MATCHED THEN
    UPDATE SET Address = S.Address;

Do you want to set the Address to a random element of the values from the source table? Use a TOP 1 and ORDER BY NEWID() clause in your subselect:

要将地址设置为源表中值的随机元素吗?在您的子选择中使用TOP 1和ORDER BY NEWID()子句:

MERGE #T 
USING (SELECT TOP 1 id, name, address FROM #S ORDER BY NEWID()) AS S
ON #T.id = S.id AND #T.Name = S.Name
WHEN NOT MATCHED THEN
    INSERT VALUES(S.id,S.Name, S.Address)
WHEN MATCHED THEN
    UPDATE SET Address = S.Address;

If you match four source rows to a single target row, you'll never get a useful result - you need to know what you really want.

如果您将四个源行与一个目标行匹配，您将永远不会得到有用的结果——您需要知道您真正想要什么。

Marc

马克

#2

Remove the dupicate using

删除dupicate使用

select R.* 
from  (SELECT Customer,Material,Received_date_time,
row_number() over (Partition by Customer, Material   
order by  Customer,Material,Received_date_time) as rn
      from Customer_Table WHERE Status=0     
     ) as R
where R.rn = 1

for merge you cannot have duplicates, so you always have to pick up the latest

对于merge，您不能有重复，因此您必须选择最新的

#1