March 12, 2019 at 1:37 pm
I have Table with date , itemid , amount
03/01/2019 1 45
03/01/2019 2 46
i got data from other table and my result have this data
03/01/2019 1 40
03/01/2019 1 42
03/01/2019 2 35
03/01/2019 2 25
03/01/2019 3 40
I am truing to use merge statement so when match adding data for 1 as 45 + 42 +40 when not matching i am inserting data
but i get this error
Msg 8672, Level 16, State 1, Line 891
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.
what is best approach to work around i dont want use cursor for this one.
March 12, 2019 at 2:50 pm
It's exactly what it says. There are two matches for itemid = 1, so it doesn't know which record to use in the merge. Given your desired result, you'll want to SUM the amounts in the second table before merging with the first.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 13, 2019 at 7:54 am
Try this on for size:CREATE TABLE #TEST1 (
TheDate date,
itemid int,
amount int
);
INSERT INTO #TEST1 (TheDate, itemid, amount)
VALUES ('03/01/2019', 1, 45),
('03/01/2019', 2, 46);
CREATE TABLE #TEST2 (
TheDate date,
itemid int,
amount int
);
INSERT INTO #TEST2 (TheDate, itemid, amount)
VALUES ('03/01/2019', 1, 40),
('03/01/2019', 1, 42),
('03/01/2019', 2, 35),
('03/01/2019', 2, 25),
('03/01/2019', 3, 40);
WITH CTE AS (
SELECT
TheDate,
itemid,
SUM(amount) AS amount
FROM #TEST2
GROUP BY
TheDate,
itemid
)
MERGE INTO #TEST1 AS T
USING CTE AS C
ON T.TheDate = C.TheDate
AND T.itemid = C.itemid
WHEN MATCHED THEN
UPDATE SET T.amount = T.amount + C.amount
WHEN NOT MATCHED THEN
INSERT (TheDate, itemid, amount)
VALUES (C.TheDate, C.itemid, C.amount);
SELECT *
FROM #TEST1;
DROP TABLE #TEST1;
DROP TABLE #TEST2;
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 13, 2019 at 8:02 am
You will get better performance from an UPDATE followed by an INSERT than you'll get from a MERGE.
March 14, 2019 at 6:25 am
Jonathan AC Roberts - Wednesday, March 13, 2019 8:02 AMYou will get better performance from an UPDATE followed by an INSERT than you'll get from a MERGE.
Thanks everyone.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply