sql - SQL Server MERGE 语句的问题

Question

源表

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

目标表

Id, Name, Address
1   A     NULL

合并后

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

我正在使用这个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 

这会导致错误

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.

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

Answer 1

#S 中的任何四个值都将与目标表的单行值匹配(#S 中的所有值的 id = 1 且 name = 'A' - 因此它们都与单行值匹配在目标中)，因此这个值将被更新四次 - 这就是错误所说的，而且它是绝对正确的。

你真正想在这里实现什么？

是否要将地址设置为源表中的第一个值？在子选择中使用 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;

是否要将地址设置为源表中值的随机元素？在子选择中使用 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;

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

马克