March 14, 2016 at 11:48 pm
Hi,
I have target table & source table. Even though I don't have any duplicate records in tables, I got the error message as "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.".
Please let me know what could be the possible reason for this?
here is the script related to that merge statement
MERGE dbo.eComSize AS Target
USING (
SELECT * FROM eComSizeTemp
) AS Source
ON (Target.article = Source.article AND
Target.sizeIndex = LTRIM(RTRIM(Source.sizeIndex)) AND
Target.market_id = LTRIM(RTRIM(Source.market_id)))
WHEN MATCHED THEN
UPDATE
SET
Target.[ean] = LTRIM(RTRIM(ISNULL(Source.[ean], Target.[ean]))),
Target.[upc] = LTRIM(RTRIM(ISNULL(Source.[upc], Target.[upc]))),
Target.[literalSize] = LTRIM(RTRIM(Source.[literalSize]))
WHEN NOT MATCHED BY TARGET THEN
INSERT ([article]
,[sizeIndex]
,[ean]
,[upc]
,[market_id],[literalSize])
VALUES(LTRIM(RTRIM(Source.[article])),
LTRIM(RTRIM(Source.[sizeIndex])),
LTRIM(RTRIM(Source.[ean])),
LTRIM(RTRIM(Source.[upc])),
LTRIM(RTRIM(Source.[market_id])),
LTRIM(RTRIM(Source.[literalSize])))OUTPUT $action INTO @SummaryOfChanges;
Source table records:-
article sizeIndexupc market_idliteralSize
101570054871016745cf6.5
101580054871016752cf7
101590054871016769cf7.5
101600054871016776cf8
101610054871016783cf8.5
101620054871016790cf9
101630054871016806cf9.5
101640054871016813cf10
101650054871016820cf10.5
101660054871016837cf11
101670054871016844cf11.5
101680054871016851cf12
101700054871016868cf13
101690722977171452cf12.5
101720722977849504cf14
101740722977849511cf15
101710885589781607cf13.5
101730885589781614cf14.5
101560885589799855cf6
101530889131549196cf4.5
101550889131549202cf5.5
101510889131549219cf3.5
101540889131549226cf5
101520889131549233cf4
Target table records
article sizeIndex upc market_idliteralSize
101510889131549219us3.5
101520889131549233us4
101530889131549196us4.5
101540889131549226us5
101550889131549202us5.5
101560885589799855us6
101570054871016745us6.5
101580054871016752us7
101590054871016769us7.5
101600054871016776us8
101610054871016783us8.5
101620054871016790us9
101630054871016806us9.5
101640054871016813us10
101650054871016820us10.5
101660054871016837us11
101670054871016844us11.5
101680054871016851us12
101690722977171452us12.5
101700054871016868us13
101710885589781607us13.5
101720722977849504us14
101730885589781614us14.5
101740722977849511us15
Regards
Soumya
March 15, 2016 at 9:51 am
The problem is that your ON clause must not identify unique rows.
The other issue with your post is that the MERGE statement includes an ean column and the sample data does not, so it is difficult to help. The only thing I can come up with is that ean should be included in the ON clause because the sample data you provided works just fine.
DECLARE @Source TABLE
(
Article INT,
SizeIndex INT,
upc VARCHAR(25),
Market_ID CHAR(2),
LiteralSize DECIMAL(10, 2)
);
DECLARE @Target TABLE
(
Article INT,
SizeIndex INT,
upc VARCHAR(25),
Market_ID CHAR(2),
LiteralSize DECIMAL(10, 2)
);
INSERT INTO @Source
(Article, SizeIndex, upc, Market_ID, LiteralSize)
VALUES
(101, 570, '054871016745', 'cf', 6.5),
(101, 580, '054871016752', 'cf', 7),
(101, 590, '054871016769', 'cf', 7.5),
(101, 600, '054871016776', 'cf', 8),
(101, 610, '054871016783', 'cf', 8.5),
(101, 620, '054871016790, ', 'cf', 9),
(101, 630, '054871016806', 'cf', 9.5),
(101, 640, '054871016813', 'cf', 10),
(101, 650, '054871016820', 'cf', 10.5),
(101, 660, '054871016837', 'cf', 11),
(101, 670, '054871016844', 'cf', 11.5),
(101, 680, '054871016851', 'cf', 12),
(101, 700, '054871016868', 'cf', 13),
(101, 690, '722977171452', 'cf', 12.5),
(101, 720, '722977849504', 'cf', 14),
(101, 740, '722977849511', 'cf', 15),
(101, 710, '885589781607', 'cf', 13.5),
(101, 730, '885589781614', 'cf', 14.5),
(101, 560, '885589799855', 'cf', 6),
(101, 530, '889131549196', 'cf', 4.5),
(101, 550, '889131549202', 'cf', 5.5),
(101, 510, '889131549219', 'cf', 3.5),
(101, 540, '889131549226', 'cf', 5),
(101, 520, '889131549233', 'cf', 4);
INSERT INTO @Target
(Article, SizeIndex, upc, Market_ID, LiteralSize)
VALUES
(101, 510, '889131549219', 'us', 3.5),
(101, 520, '889131549233', 'us', 4),
(101, 530, '889131549196', 'us', 4.5),
(101, 540, '889131549226', 'us', 5),
(101, 550, '889131549202', 'us', 5.5),
(101, 560, '885589799855', 'us', 6),
(101, 570, '054871016745', 'us', 6.5),
(101, 580, '054871016752', 'us', 7),
(101, 590, '054871016769', 'us', 7.5),
(101, 600, '054871016776', 'us', 8),
(101, 610, '054871016783', 'us', 8.5),
(101, 620, '054871016790', 'us', 9),
(101, 630, '054871016806', 'us', 9.5),
(101, 640, '054871016813', 'us', 10),
(101, 650, '054871016820', 'us', 10.5),
(101, 660, '054871016837', 'us', 11),
(101, 670, '054871016844', 'us', 11.5),
(101, 680, '054871016851', 'us', 12),
(101, 690, '722977171452', 'us', 12.5),
(101, 700, '054871016868', 'us', 13),
(101, 710, '885589781607', 'us', 13.5),
(101, 720, '722977849504', 'us', 14),
(101, 730, '885589781614', 'us', 14.5),
(101, 740, '722977849511', 'us', 15);
MERGE @Target AS Target
USING
(
SELECT
*
FROM
@Source AS S
) AS Source
ON (
Target.article = Source.article AND
Target.sizeIndex = LTRIM(RTRIM(Source.sizeIndex)) AND
Target.market_id = LTRIM(RTRIM(Source.market_id))
)
WHEN MATCHED THEN
UPDATE SET
--Target.[ean] = LTRIM(RTRIM(ISNULL(Source.[ean], Target.[ean]))),
Target.[upc] = LTRIM(RTRIM(ISNULL(Source.[upc], Target.[upc]))),
Target.[literalSize] = LTRIM(RTRIM(Source.[literalSize]))
WHEN NOT MATCHED BY TARGET THEN
INSERT
(
[article],
[sizeIndex],
--[ean],
[upc],
[market_id],
[literalSize]
)
VALUES (
LTRIM(RTRIM(Source.[article])),
LTRIM(RTRIM(Source.[sizeIndex])),
--LTRIM(RTRIM(Source.[ean])),
LTRIM(RTRIM(Source.[upc])),
LTRIM(RTRIM(Source.[market_id])),
LTRIM(RTRIM(Source.[literalSize]))
)
OUTPUT
$action
;
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply