sql - Merge Statement inserting duplicate rows? -

for eternity have been spending time on this, can't seem find cause why merge statement inserts duplicate rows. here 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, fast food delivery can contain multiple orders in 1 transaction number.

when run query...

merge inventory t1 using incomingstocks t2 on t1.productcode = t2.productcode , t2.requestnumber = 'request123' , t2.deliverylocation= 'basement' when matched update set t1.quantity = t1.quantity + t2.quantity when not matched insert (productid, productname, productcode, quantity, location)  values (t2.productid, t2.productname, t2.productcode, t2.quantity, t2.deliverylocation); 

...it returns 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 inserted! duplicated stabilo (which not in incomingstocks table, added quantity of parker pens (3+4) , reinserted again time initial quantity.

please, can me? insight or comment regarding query? there wrong it?

thank you!!!

i kinda didnt understand t2.destinationlocation, t2.location, using incoming stocks t2

anyways try this:

merge inventory t1 using incomingstocks t2 on t1.productcode = t2.productcode , t2.requestnumber = 'request123' , t2.deliverylocation = 'basement' when matched update set t1.quantity = t1.quantity + t2.quantity when not matched insert (productid, productname, productcode, quantity, location)  values (t2.productid, t2.productname, t2.productcode, t2.quantity, t2.deliverylocation);  select * inventory