Merge Statement inserting duplicate rows?

Question

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 1

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