源表
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值.怎么办？
解决方法
#S中的任何四个值都将与目标表的单行值匹配(#S中的所有值都为id = 1,name =’A’ – 因此它们都与目标中的单个行匹配),因此此值将更新了四次 – 这是错误说的,这是绝对正确的.
你真的想在这里实现什么？
要将地址设置为源表中的第一个值吗？在您的子选择中使用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;
如果您将四个源行匹配到单个目标行,那么您将永远不会得到有用的结果 – 您需要知道您真正想要的内容.
渣子
