Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Issues with SQL Server MERGE statement

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?

like image 716
R.D Avatar asked Sep 16 '09 16:09

R.D


People also ask

Should you use MERGE in SQL?

Using the MERGE statement in SQL gives you better flexibility in customizing your complex SQL scripts and also enhances the readability of your scripts. The MERGE statement basically modifies an existing table based on the result of comparison between the key fields with another table in the context.

Is MERGE better than update?

The UPDATE statement will most likely be more efficient than a MERGE if the all you are doing is updating rows. Given the complex nature of the MERGE command's match condition, it can result in more overhead to process the source and target rows.

Is MERGE faster than update in SQL Server?

The basic set-up data is as follows. We've purposely set up our source table so that the INSERTs it will do when merged with the target are interleaved with existing records for the first 500,000 rows. These indicate that MERGE took about 28% more CPU and 29% more elapsed time than the equivalent INSERT/UPDATE.


2 Answers

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

like image 193
marc_s Avatar answered Sep 19 '22 07:09

marc_s


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

like image 45
Richard Varghese Avatar answered Sep 20 '22 07:09

Richard Varghese