CODEWITHSUNDEEP
mysqlsql
Taqwa
Taqwa

Reputation: 29

Trigger that updates the average from another table

I have two tables: Product and Review. I'm trying to make a trigger that updates the product table(column:stars) when a new review is inserted. They are connected by product_id. I tried this code but I guess its not right.

Code:

create trigger update_stars after insert on Review for each row 
update Product
set stars=(select avg(stars) from Review where product_id=Product.product_id)
where product_id=new.product_id;

ERD

Upvotes: 1

Views: 712

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133390

for the sql code you could use an update based on a inner join eg: 

update Product p
inner join (
  select product_id, avg(stars) avg_stars
  from Review
  group by product_id
) t on t.product_id = p.product_id
set stars=t.avg_stars

Upvotes: 1

Related Questions