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!
Best Answer