CODEWITHSUNDEEP
mysqlsqlsql-update
Wolftacular
Wolftacular

Reputation: 13

UPDATE with an avg() from another table?

USING MYSQL

I had to create an average field for a table Movies to hold the average score for each movie. The actual scores are held in a table Ratings, which references Movies by mid (movie id). How would you go about updating each row in movies with the average found from the column "score" in Ratings?

I tried JOINS but they don't seem to work with MySQL. Any suggestions? Thank you in advance!

Upvotes: 1

Views: 1708

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

You can do this with an update and join:

update movies m join
       (select mid, avg(score) as avgscore
        from ratings r
        group by mid
       ) r
       on m.mid = r.mid
    set m.avgscore = r.avgscore;

Of course, this will update the value only once. If you want to keep the averages up-to-date, then you need to create a trigger to re-set the value.

Upvotes: 4

Related Questions