How to use update to insert a new value in another table MySQL-CodePudding

I'm new to triggers in MySQL. I'm trying to update the column "product_in_stock" from the table "product". For that i'm using a trigger that after an insert on "orders" table, it will make an subtraction of product_in_stock (from product) with quantity (from orders).

These is my trigger so far:

CREATE TRIGGER 
    quantityupdate
AFTER INSERT ON orders FOR EACH ROW
UPDATE 
    product
SET 
    product.product_in_stock = product.product_in_stock- orders.quantity
WHERE 
    product.product_id = NEW.product_id;

The tables (just an example)

Product

product_id	product_in_stock
20	10

Orders

orders_id	quantity (bough)
First	5

Expected outcome:

Product

product_id	product_in_stock
20	5

CodePudding user response：

Try to use:

CREATE TRIGGER 
    quantityupdate
AFTER INSERT ON orders FOR EACH ROW
UPDATE 
    product
SET 
    product.product_in_stock = product.product_in_stock- new.quantity
WHERE 
    product.product_id = NEW.product_id;