mysql update table from another table

Question

Hi, I'm trying to update a field in one table, from the sum of another field, in another table.

company_tbl (PRIMARY, companySize, companyName) location_tbl (PRIMARY, companyID, locationSize, locationName)

The two tables link by company_tbl.PRIMARY = location_tbl.companyID

update company_tbl comp, location_tbl loc
set companySize = sum(locationSize)
where comp.PRIMARY = loc.companyID

I'm getting an error of 'invalid use of group function'

A company can have multiple locations

Is what I want to do possible? I want to take the sum of locations, that belong to a specific company, and update the companySize with the sum.

Thanks!

Answer 1

Use:

UPDATE company_tbl comp
   SET companySize = (SELECT SUM(lt.locationSize)
                        FROM location_tbl lt
                       WHERE lt.companyid = comp.primary)

...or you could use a view, containing:

   SELECT c.primary,
          COALESCE(SUM(lt.locationsize), 0) AS companysize
     FROM company_tbl c
LEFT JOIN location_tbl lt ON lt.companyid = c.primary

Answer 2

First initialize the companySize to zero:

UPDATE company_tbl SET companySize = 0; 

Then for each matching location row, add the locationSize:

UPDATE company_tbl comp JOIN location_tbl loc ON comp.PRIMARY = loc.companyID
SET comp.companySize = comp.companySize + loc.locationSize;

You get the desired sum by the time it has processed all the matching locations for each company.