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
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?
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With