CODEWITHSUNDEEP
mysql
user3004517
user3004517

Reputation: 1

Updating MYSQL with average score

I have two tables fluids and cas and I'm trying to update a related table fluids with the average score from the cad table. Something like this:

UPDATE fluids INNER JOIN cas on cas.cas = fluids.cas 
SET fluids.score = avg(cas.score) GROUP BY cas.cas

Upvotes: 0

Views: 42

Answers (1)

Vatev
Vatev

Reputation: 7590

You need to wrap your aggregate select as a subquery:

UPDATE fluids 
INNER JOIN (
    SELECT cas,avg(cas.score) as avg_score
    FROM cas
    GROUP BY cas 
) as c ON c.cas = fluids.cas 
SET fluids.score = c.avg_score

Upvotes: 1

Related Questions