Combine queries into one-CodePudding

I would like that as soon as I add a product to the "orders" table, the quantity of the product decreases by 1. I wrote two separate queries to implement this task.

Query to add data to the "orders" table:

INSERT INTO orders(id_buyer, id_product, date, status) 
VALUES (2, 8, '2022-05-22', 1);

Request to reduce "amount" by one:

update products 
set amount = amount - 1, 
amount = amount - 1 
where id in ( select id_product from orders where status = 1);

Is it possible to combine these two queries into one?

CodePudding user response：

In answer to the question (even if this is or not the right way) you can use AFTER INSERT TRIGGER

Try:

CREATE TRIGGER after_orders_insert AFTER INSERT ON orders 
FOR EACH ROW
  UPDATE products
     SET amount=amount - 1
   WHERE id = NEW.id_product
   AND NEW.status =1;

Working demo