Need help… I want to subtract the prod_piece from the customer table into prod_qty product table when every time the user purchase.(prod_piece - prod_qty)
This is my SQL:
UPDATE product o INNER JOIN customer p ON p.prod_name = o.id set o.prod_qty = o.prod_qty - p.piece;
UPDATE product o set o.total_price = o.prod_qty * o.prod_price;
The problem with this SQL is that it can subtract, but it only accepts the previous piece order by the user.
You should NOT keep a record of any amount by updating a value in a column. This does not provide an audit trail in case of a programming mistake, a double form submission, or nefarious activity.
The correct way of doing this is to insert a row into a table for every transaction that affects an amount, which is a stock quantity in your case. For stock received/loss, you would have a stock table, with columns for the item_id, quantity, and datetime. For items ordered, you would have an orders table and an order_items table. The order_items table would have columns for the order_id, item_id, and quantity. To get the current stock amount for any item(s), you would use a UNION query between the stock table and the order_items table, to add the positive amounts that increase the stock quantity and subtract the negative amounts that decrease the stock quantity.
