MySQL trigger update row after insert where

mysql, sql-update, triggers

Solution

You are indeed updating all rows from Recipes every time your trigger triggers. Using the NEW.Recipe_No pseudo column, you can restrict your update to only the affected Recipes record:

CREATE TRIGGER update_avg AFTER INSERT ON `Ratings`
FOR EACH ROW UPDATE Recipes
  SET Rating_Avg = (SELECT AVG(Rating) from Ratings where Ratings.Recipe_No=Recipes.Recipe_No)
WHERE Recipes.Recipe_No=NEW.Recipe_No

Problem

I have 2 tables, `Ratings` and `Recipes`. After insert on `Ratings` I need to find the average of all ratings for the rated recipe, and update the `Rating_Avg` column in the `Recipes` table. This works but I believe it is updating all rows in `Recipes.Rating_Avg` when I just need to update the row where Recipe_No = the most recently rated Recipe_No. ``` CREATE TRIGGER `update_avg` AFTER INSERT ON `Ratings` FOR EACH ROW UPDATE Recipes SET Rating_Avg = (SELECT AVG(Rating) from Ratings where Ratings.Recipe_No=Recipes.Recipe_No) ``` I feel like I need to add a `WHERE Recipe_No = NEW.Recipe_No` but I'm not sure where to add it.

Original source