If it updates all the inventory records, it is because the where clause is wrong. The Where part allows you to limit the records you want to update.
If you look closely, in your where you are limiting the update to all products that have the “purchasequantity” field equal to the $quantity variable. This is clearly not going to work for you, because the limitation of the where would have to be by the identifier of the product that you want to update.
Personally, I don’t understand very well the names of your tables (for example, the buy and sell table seem the same thing to me, really). I don’t understand why you do the join either, because in principle I don’t see it as necessary. That’s why I’m giving you an example of how I think your sentence should be, with names of tables and columns that I consider more understandable.
UPDATE product SET stock = stock – 5 where product_id = 103
That would be the sentence, assuming that the identifier of the product that you want to update is 103 and that you want to subtract 5 units from the stock of that product.
Facebook Comments
Disqus