Saudate Saudate - 22 days ago 10
SQL Question

Merge Statement inserting duplicate rows?

For the eternity that I have been spending time on this, I can't seem to find the cause as to why my

MERGE
statement inserts duplicate rows. Here are my tables.
TABLE
INVENTORY


ProductID | ProductName | ProductCode | Quantity | Location
1 | Stabilo | Code123 | 3 | Basement
2 | Parker Pen | Code456 | 4 | Basement


TABLE INCOMINGSTOCKS


REQUESTNUMBER | ProductID | ProductName | ProductCode | Quantity | DeliveryLocation
Request123 | 2 | Parker Pen | Code456 | 3 | Basement
Request123 | 3 | Eraser | Code789 | 5 | Basement


One request number = multiple items, much like a fast food delivery can contain multiple orders in one transaction number.


When I run this query...


MERGE INVENTORY as T1
USING INCOMINGSTOCKS AS T2
ON T1.ProductCode = T2.ProductCode
AND T2.REQUESTNUMBER = 'Request123' and T2.DeliveryLocation= 'Basement'
WHEN MATCHED THEN
UPDATE SET T1.Quantity = T1.Quantity + T2.Quantity
WHEN NOT MATCHED THEN
INSERT (ProductID, ProductName, ProductCode, Quantity, Location)
VALUES (T2.ProductID, T2.ProductName, T2.ProductCode, T2.Quantity, T2.DeliveryLocation);




...it returns with this data:


ProductID | ProductName | ProductCode | Quantity | Location
Stabilo | 1 | Code123 | 3 | Basement
Stabilo | 1 | Code123 | 3 | Basement
Parker Pen | 2 | Code456 | 7 | Basement
Parker Pen | 2 | Code456 | 4 | Basement


The "Eraser" item did not even get inserted! It only duplicated Stabilo (which was not in the
INCOMINGSTOCKS
table, added the quantity of
Parker Pens
(3+4) and reinserted it again this time with its initial quantity.



Please, can someone help me? Any insight or any comment regarding my query? Is there something wrong with it?


THANK YOU!!!

Answer

I kinda didnt understand T2.DestinationLocation, T2.Location, USING INCOMING STOCKS AS T2

Anyways try like this:

MERGE INVENTORY as T1
USING INCOMINGSTOCKS AS T2
ON T1.ProductCode = T2.ProductCode
and T2.REQUESTNUMBER = 'Request123' and T2.DeliveryLocation = 'Basement'
WHEN MATCHED THEN
UPDATE SET T1.Quantity = T1.Quantity + T2.Quantity
WHEN NOT MATCHED THEN
INSERT (ProductID, ProductName, ProductCode, Quantity, Location) 
VALUES (T2.ProductID, T2.ProductName, T2.ProductCode, T2.Quantity, T2.DeliveryLocation);

select * from INVENTORY
Source (Stackoverflow)
Comments