Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Merge Error : The MERGE statement attempted to UPDATE or DELETE [duplicate]

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

  1. Insert if not exists
  2. update if BGID matches
  3. 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?

like image 804
Nikhil Agrawal Avatar asked Oct 24 '16 11:10

Nikhil Agrawal


1 Answers

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.

like image 91
gofr1 Avatar answered Oct 17 '22 13:10

gofr1