SQL Server MERGE语句的问题

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

目标表

Id,Address
1   A     NULL

合并后

Id,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,Address varchar(25) NULL)

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

 INSERT #T values(1,NULL)

 MERGE #T USING
  (
Select id,name,address 
from #S
  ) AS S(id,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中的行的Address值.怎么办？

解决方法

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

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

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

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

MERGE #T 
USING (SELECT TOP 1 id,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.Address)
WHEN MATCHED THEN
    UPDATE SET Address = S.Address;

如果您将四个源行匹配到单个目标行,那么您将永远不会得到有用的结果 – 您需要知道您真正想要的内容.

渣子

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!