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 pr...