0

I have tables companies and employees:

 explain companies;
+--------------------+---------------+------+-----+---------+----------------+
| Field              | Type          | Null | Key | Default | Extra          |
+--------------------+---------------+------+-----+---------+----------------+
| id                 | int(11)       | NO   | PRI | NULL    | auto_increment |
| score              | int(11)       | NO   | MUL | NULL    |                |
+--------------------+---------------+------+-----+---------+----------------+


 explain employees;
+--------------------+---------------+------+-----+---------+----------------+
| Field              | Type          | Null | Key | Default | Extra          |
+--------------------+---------------+------+-----+---------+----------------+
| id                 | int(11)       | NO   | PRI | NULL    | auto_increment |
| company_id         | int(11)       | NO   | PRI | NULL    |                |
| score              | int(11)       | NO   | MUL | NULL    |                |
+--------------------+---------------+------+-----+---------+----------------+

What I'd like to do is update the companies.score column based on the SUM of the employees.score column where employees.company_id = companies.id.

So given Company A and Company B, and Employee A1 (score 5), Employee A2 (score 2), and Employee B1 (score 3); I want to update Company A's score to 7, and Company B's score to 3.

What's the most elegant way to achieve this in MySQL?

Thanks!

2
update companies c set c.score = (select sum(e.score) from employees e where e.company_id = c.id);
1
  • 2
    Embarrassingly - I had the answer in the question :(
    – bodacious
    Dec 27 '13 at 16:26
2

Although a subquery based approach gives you valid results probably a better approach is to use a multi-table update syntax

UPDATE companies c JOIN
(
  SELECT company_id, SUM(score) score
    FROM employees
   GROUP BY company_id
) e
    ON c.id = e.company_id
   SET c.score = e.score;

Here is SQLFiddle demo

2
  • Thanks for your input - why is joining on a subquery better than the answer provided by Balazs Papp?
    – bodacious
    Dec 30 '13 at 11:39
  • 1
    You're quite welcome :) A correlated subquery is executed for each row in your update statement. When you do it with a JOIN only two queries are issued 1) to calculate totals and 2) to update
    – peterm
    Dec 31 '13 at 21:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.