How to use trigger to update only one row in table with Mysql

Question

I have an after insert trigger that is supposed to update the field total in my table "test" where the id_cart is equal to new.id_cart. However my trigger is updating every single row in the table not only the one desired. I would like to know how can I modify my trigger so it only updates the row that I want. This is my trigger.

CREATE DEFINER=`root`@`localhost` TRIGGER `update_total_test` 
AFTER INSERT ON `test_product_quantity_cart` 
FOR EACH ROW BEGIN
UPDATE test set total= (select sum(price_product) from test_product_quantity_cart where id_cart=new.id_cart);

END

So if the new row inserted in table "test_product_quantity_cart" has an new.id_cart=1, then only the row in table "test" with id_cart=1 should be uptated. I think I am missing a "where" clause to indicate the update statement which rows it is suppossed to upate. However I do not know how to add that clause. Thank you!

Answer 1

CREATE DEFINER=`root`@`localhost` TRIGGER `update_total_test` 
AFTER INSERT ON `test_product_quantity_cart` 
FOR EACH ROW
UPDATE test 
  JOIN ( SELECT id_cart, SUM(price_product) total
         FROM test_product_quantity_cart 
         WHERE id_cart=NEW.id_cart ) value_for_update USING (id_cart)
SET test.total = value_for_update.total;