CODEWITHSUNDEEP
mysqlupdatesbulkbulkupdate
Awan
Awan

Reputation: 18560

Update table based on result of select on another table

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;

Upvotes: 1

Views: 117

Answers (2)

Drew
Drew

Reputation: 24949

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.

Upvotes: 2

Paul L
Paul L

Reputation: 938

 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.

Upvotes: 1

Related Questions