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

possible duplicate of [SQL Server Update Group by](http://stackoverflow.com/questions/2853403/sql-server-update-group-by) — Guillermo Gutiérrez, Jan 29 '15 at 17:04

Joachim Isaksson · Accepted Answer · 2012-10-07T08:31:20.563

12

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 :)

edited Oct 07 '12 at 08:31

answered Oct 07 '12 at 08:07

Joachim Isaksson

176,943
25
281
294

that works I know. can shortly explain me why is it simpler and how it would be by using group by & join – levi Oct 07 '12 at 08:12

score 0 · Answer 2 · answered Oct 14 '16 at 04:08

 UPDATE ComisionesxColaboradorxLineaPrescripciones
 SET CANTIDAD_PRODUCTOS_CORE_CUMPLE = CANTIDAD
 FROM @ComisionesxColaboradorxLineaPrescripciones ComisionesxColaboradorxLineaPrescripciones
 INNER JOIN
    (SELECT TAB_L.COD_COLAB AS COD_COLAB,TAB_L.TIPO_COLABORADOR AS TIPO_COLABORADOR, COUNT(TAB_P.COD_SEG) AS CANTIDAD
    FROM @ComisionesxColaboradorxLineaPrescripciones TAB_L
    INNER JOIN @ComisionesxColaboradorxLineaxProductoPrescripciones TAB_P
    ON TAB_L.COD_COLAB=TAB_P.COD_COLAB AND
    TAB_L.TIPO_COLABORADOR=TAB_P.TIPO_COLABORADOR
    GROUP BY TAB_L.COD_COLAB,TAB_L.TIPO_COLABORADOR
    ) AGRUPADO 
        ON ComisionesxColaboradorxLineaPrescripciones.COD_COLAB = AGRUPADO.COD_COLAB AND
        ComisionesxColaboradorxLineaPrescripciones.TIPO_COLABORADOR = AGRUPADO.TIPO_COLABORADOR

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. — Andre Figueiredo, Apr 27 '17 at 14:30

Update Table by Join and Group by

2 Answers2