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; 

Answer 1

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.