SQL Server MERGE 语句的问题
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1434028/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Issues with SQL Server MERGE statement
提问by R.D
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.
消息 8672，级别 16，状态 1，第 18 行
MERGE 语句尝试多次更新或删除同一行。当目标行匹配多个源行时会发生这种情况。MERGE 语句不能多次更新/删除目标表的同一行。细化 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 中的行。这该怎么做？
回答by marc_s
Any of the four values in #Swill 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.
中的四个值中的任何一个#S都将匹配目标表的单行值（#S 中的所有值都具有 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 1clause 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 1and ORDER BY NEWID()clause in your subselect:
是否要将地址设置为源表中值的随机元素？在您的子选择中使用TOP 1andORDER 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
马克
回答by Richard Varghese
Remove the dupicate using
删除重复使用
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
对于合并，您不能有重复项，因此您必须始终选择最新的
