[Solved] mysql update qty on complete order array [duplicate]

Question

Probably best to do this as a single piece of SQL:-

UPDATE stock a INNER JOIN order b ON a.part = b.part 
SET a.available = a.available - b.qty
WHERE  b.invoice` = '$order'

Watch out that you don’t just rerun this multiple times without some way of checking that an order hasn’t already been used to update the stock

Doing it this way, if you had 1000 items on the order then it is a single query. Doing a select and then looping around the results would require 10001 queries.

Accepted Answer

Probably best to do this as a single piece of SQL:-

UPDATE stock a INNER JOIN order b ON a.part = b.part 
SET a.available = a.available - b.qty
WHERE  b.invoice` = '$order'

Watch out that you don’t just rerun this multiple times without some way of checking that an order hasn’t already been used to update the stock

Doing it this way, if you had 1000 items on the order then it is a single query. Doing a select and then looping around the results would require 10001 queries.