Issues with SQL Server MERGE statement

merge, sql, sql-server, sql-server-2008, t-sql

Solution

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

Problem

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?

Original source