Update table based on result of select on another table

Question

I have two tables like this:

person:

id | name | sale | commission
1  | abc  | 0    |   0
2  | xyz  | 0    |   0

sale:

id | date       | person_id | sale | commission
1  | 2016-05-01 |     1     | 10   |     1
2  | 2016-05-02 |     1     | 10   |     1
3  | 2016-05-03 |     1     | 10   |     1
4  | 2016-05-01 |     2     | 20   |     2
5  | 2016-05-02 |     2     | 20   |     2
6  | 2016-05-01 |     2     | 20   |     2

I want to update person table with single update query and change the table something like this:

person:

id | name | sale | commission
1  | abc  | 30   |   3
2  | xyz  | 60   |   6

I know I can sum sale like following but how to update following query result into person table directly.

SELECT person_id, SUM(sale), SUM(commission) 
FROM sale
GROUP BY person_id;

as a future reference, update with a join pattern http://stackoverflow.com/q/15209414 — Drew, Jun 01 '16 at 15:54
@Drew I'm trying and failing to understand how that would work with the aggregation factor. Would you mind posting an answer that uses the join solution? I'm sure that's the better way than my answer, but I'm not seeing how to do it. — Paul L, Jun 01 '16 at 16:06
@PaulL I provided an answer to show it. My reference above (the first comment) wasn't exactly a roadmap for helping with aggregates. Sorry about that. — Drew, Jun 01 '16 at 16:51

Drew · Accepted Answer · 2016-06-01T16:58:01.273

As Strawberry said in the comments under your question, think long and hard before you save this information. It is denormalized, and it becomes stale. Rather, consider using it during report generation. Otherwise, well, as said, you may run into problems.

drop table if exists person;
create table person
(   personId int auto_increment primary key,
    name varchar(100) not null,
    totSales decimal(9,2) not null,
    totComm decimal(9,2)
);
insert person(name,totSales,totComm) values
('Joe',0,0),
('Sally',0,0);
-- just added persons 1 and 2 (auto_inc)

drop table if exists sale;
create table sale
(   saleId int auto_increment primary key,
    saleDate date not null,
    personId int not null,
    sale decimal(9,2) not null,
    commission decimal(9,2) not null,
    index(personId), -- facilitate a snappier "group by" later
    foreign key (personId) references person(personId) -- Ref Integrity
);

insert sale(saleDate,personId,sale,commission) values
('2016-05-01',2,10,1),
('2016-05-01',1,40,4),
('2016-05-02',1,30,3),
('2016-05-07',2,10,1),
('2016-05-07',2,90,9);

-- the following dies on referential integrity, FK, error 1452 as expected
insert sale(saleDate,personId,sale,commission) values ('2016-05-01',4,10,1);

The update statement

update person p 
join  
(   select personId,sum(sale) totSales, sum(commission) totComm 
    from sale 
    group by personId 
) xDerived 
on xDerived.personId=p.personId 
set p.totSales=xDerived.totSales,p.totComm=xDerived.totComm;

The results

select * from person;
+----------+-------+----------+---------+
| personId | name  | totSales | totComm |
+----------+-------+----------+---------+
|        1 | Joe   |    70.00 |    7.00 |
|        2 | Sally |   110.00 |   11.00 |
+----------+-------+----------+---------+
2 rows in set (0.00 sec)

xDerived is merely an alias name. All derived tables need an alias name, whether or not you use the alias name explicitly.

Thanks for detail and useful answer. I am using this information for quick report generation only. I have tried to show my problem using simplest example. — Awan, Jun 02 '16 at 07:02

Paul L · Answer 2 · 2016-06-01T16:12:01.990

1

 UPDATE person
 SET sale = (
    SELECT SUM(s.sale) FROM sale s
    WHERE s.person_id = person.id
 );

works for me. See it in action at: http://ideone.com/F32oUU

EDIT for new version with additional aggregated column:

UPDATE person SET 
 sale = (
    SELECT SUM(s.sale) FROM sale s
    WHERE s.person_id = person.id
 ),
 commission = (
    SELECT SUM(s.commission) FROM sale s
    WHERE s.person_id = person.id
 );

http://ideone.com/yo1A9Y

This being said, I feel sure that a JOIN solution is better, and am hopeful another answerer will be able to post such a solution.

edited Jun 01 '16 at 16:12

answered Jun 01 '16 at 16:02

Paul L

939
4
14

+1 Thanks for the answer. What if I have another column commission. Can you please review the question again. Should I use 2 sub-queries in this case. Sorry for late question update – Awan Jun 01 '16 at 16:08
yes. It is a solution but can I avoid these multiple sub-queries for sale and commission separately. – Awan Jun 01 '16 at 16:18
yup, see @Drew's fantastic answer. – Paul L Jun 01 '16 at 17:04

Update table based on result of select on another table

2 Answers2

The update statement

The results