SQL Server MERGE 语句的问题 [英] Issues with SQL Server MERGE statement

问题描述

源表

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

I am using this 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 

这会导致错误

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

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 中的行.如何做到这一点?

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

推荐答案

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

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.

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

What is it you really want to achieve here??

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

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:

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() 子句:

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:

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.

马克

这篇关于SQL Server MERGE 语句的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！