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?