sql - MySQL join update: Updating join table row twice -

to explain problem have outlined example situation below.

orderrows

id|ordernumber|productnumber|quantity|done 1 |10         |100          |1       |no* 2 |10         |101          |1       |no 3 |10         |100          |4       |no* 

*have same product number

stock productnumber|quantity 100          |5 101          |1  update orderrows  inner join stock on stock.productnumber=orderrows.productnumber  set done='yes'    ,stock. quantity = stock. quantity -orderrows. quantity  ordernumber='100' , done='no' 

the result table stock below.

productnumber|quantity 100          |4 101          |0 

the order rows updated correctly. order row 3 stock not adapted. because order row 3 has same product number order row one. question is, how can fix this?

you need use group by here. can't use directly. can achieve using sub-query this:

update orderrows o   join stock s     on s.productnumber = o.productnumber   join       (        select productnumber, sum(quantity) quantity          orderrows           done = 'no'         group productnumber      ) o1    on o.productnumber = o1.productnumber   set o.done = 'yes'      ,s.quantity = s.quantity - o1.quantity  o.done = 'no'; 

tables after update:

orderrows

| id | ordernumber | productnumber | quantity | done | ------------------------------------------------------ |  1 |          10 |           100 |        1 |  yes | |  2 |          10 |           101 |        1 |  yes | |  3 |          10 |           100 |        4 |  yes | 

stock

| productnumber | quantity | ---------------------------- |           100 |        0 | |           101 |        0 | 

see this sqlfiddle