ITKeyword,专注技术干货聚合推荐

注册 | 登录

解决Issues with SQL Server MERGE statement

itPublisher 分享于

2020腾讯云双十一活动,全年最低!!!(领取3500元代金券),
地址https://cloud.tencent.com/act/cps/redirect?redirect=1073

【阿里云】双十一活动,全年抄底价,限时3天!(老用户也有),
入口地址https://www.aliyun.com/1111/home

推荐:SQL Server 2008 Merge语句的OUTPUT功能

SQL Server 2005中的Output功能可以把Insert,Update和Delete的内容都返回,2008中的Output同样具有此功能,下面介绍一下把Output同2008的新T-SQL语句Merge组合

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

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.

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

sql sql-server tsql sql-server-2008 merge
|
  this question
edited Nov 18 '14 at 9:36 marc_s 477k 101 922 1092 asked Sep 16 '09 at 16:20 DotDot1 1,719 8 30 45

 | 

2 Answers
2

解决方法

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??

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;

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.

Marc


|
  this answer
edited Sep 16 '09 at 17:01 answered Sep 16 '09 at 16:30 marc_s 477k 101 922 1092      I want to update the row in A with Address value from any of the three matching values –  DotDot1 Sep 16 '09 at 16:32 2   but how do you determine which of them to use? –  HLGEM Sep 16 '09 at 16:55 2   I used Row_Number is both the source and target(view) . Then matching criteria included additional condition, if rownumbers are equal along with ( target.address isnull or (t.address = s.address)) –  DotDot1 Sep 16 '09 at 17:50      @marc i have the same issue, but with extra requirement. I want to update the last value coming from the source and also my source table include another field street that it values will be s1,s2,s3,s4. and i want to grape this result after the update is finished A s1, A s2, A s3, A s4 to use this data in another update stament. Any Idea ? –  user123456 Nov 18 '14 at 8:33

 | 

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


|
  this answer
edited May 8 '15 at 8:21 equisde 2,747 2 17 36 answered May 8 '15 at 8:04 Richard Varghese 1      Depending on the case, this makes more sense. Maybe you can add a unique constraint. –  Akira Yamamoto Dec 1 '15 at 14:18

 | 

推荐:Sql server2008中merge用法

1      /// <summary> 2 /// 修改:添加条件: AND roleModule.FuncCode = tvpRoleModule.FuncCode 3 /// </summary> 4 priva


相关阅读排行


相关内容推荐

最新文章

×

×

请激活账号

为了能正常使用评论、编辑功能及以后陆续为用户提供的其他产品,请激活账号。

您的注册邮箱: 修改

重新发送激活邮件 进入我的邮箱

如果您没有收到激活邮件,请注意检查垃圾箱。