SQL Merge Error : The MERGE statement attempted to UPDATE or DELETE
merge, sql-server, t-sql
Solution
You are joining the tables on `ON source.MappingId = target.MappingId`.
In your data sample, there are more than 1 row with same `MappingId = 185761`. So here you got:
A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times.
You need to specify some unique column combination to join the `source` and the `target` tables.
Problem
I have a source table ``` select 54371 Id, 'foo' [CreateBy], '2016-10-24 09:29:18.548'[CreateDate], 'foo'[UpdateBy], '2016-10-24 09:29:18.548'[UpdateDate], 'E'[MT], 185761[MID], 3[BGID] union select 54372, 'foo', '2016-10-24 09:30:18.548', 'foo', '2016-10-24 09:30:18.548', 'E', 185761, 2 ``` and a target table ``` select 54379 Id, 'foo' [CreateBy], '2016-10-24 09:29:18.548'[CreateDate], 'foo'[UpdateBy], '2016-10-24 10:29:18.548'[UpdateDate], 'E'[MT], 185761[MID], 3[BGID] ``` What I want is to match based on MT, MID and - Insert if not exists - update if BGID matches - delete if BGID does not matches When I use SQL Merge Statement I get error 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.` My Merge is like this ``` MERGE FooBar AS target USING ( SELECT E.[Id], E.[CreateBy], E.[CreateDate], E.[UpdateBy], E.[UpdateDate], E.[MT], E.[MID], E.[BGID] FROM @FooBar E ) AS source ON source.MID = target.MID AND source.MT = target.MT WHEN MATCHED and target.[BGID] = source.[BGID] THEN UPDATE SET target.[UpdateBy] = Source.[UpdateBy] ,target.[UpdateDate] = Source.[UpdateDate] When Matched and source.BGID <> target.BGID THEN DELETE WHEN NOT MATCHED THEN INSERT([CreateBy] ,[CreateDate] ,[UpdateBy] ,[UpdateDate] ,[MT] ,[MID] ,[BGID]) VALUES ( Source.[CreateBy] ,Source.[CreateDate] ,Source.[UpdateBy] ,Source.[UpdateDate] ,Source.[MT] ,Source.[MID] ,Source.[BGID] ); ``` What am I missing?