Update Table by Join and Group by

Question

A Company has many Reviews which has Rating Column itself.

CompID  Ratig
12  3
13  3
17  4
22  4
23  5
24  3
28  3,2

This is what I need to be set to each company by id. Now Rating In Company Column is NULL.

I've written something like this:

UPDATE Companies c
JOIN Reviews r on c.CompanyID = r.CompanyID
SET c.Rating = AVG(r.rating)
group by r.CompanyID

Solution

This should do what you want using a simple nested query, in this case probably simpler than a JOIN.

UPDATE Companies
SET Rating =
  (SELECT AVG(Rating) 
   FROM Ratings
   WHERE Companies.CompanyId = Ratings.CompId)

Simple SQLfiddle demo here.

EDIT: If you really want to use a JOIN/UPDATE FROM, it'd look something like this;

UPDATE c
SET c.Rating = r.Rating
FROM Companies c
JOIN (SELECT AVG(Rating) Rating, CompID FROM Ratings GROUP BY CompId) r
  ON c.CompanyId = r.CompId

At least to me, somewhat more complicated to read, and afaik it only works on SQL Server, but here's the SQLfiddle for that too :)

Comments

[levi]

that works I know. can shortly explain me why is it simpler and how it would be by using group by & join

[Andre Figueiredo]

omg, this is the ugliest naming of SQL objects I've seen. Anyways, your statement is the same of the second one in accepted solution.