MySQL Update existing value with Inner join

Question

I am updating a summary table from detail table using inner join as follows

drop TEMPORARY table if exists summ ;
drop TEMPORARY table if exists det  ;

create TEMPORARY table summ (id int , val int ) ;
create TEMPORARY table det (id int , val int ) ;

insert into summ(id,val) value (1,0) ;
insert into summ(id,val) value (2,0) ;

insert into det(id,val) value (1,10) ;
insert into det(id,val) value (1,10) ;
insert into det(id,val) value (1,20) ;

update summ inner join det on summ.id = det.id 
set summ.val = summ.val+ det.val  ;

select * from summ  where id = 1;

Its showing Value of val is 10 instead of 40 .. what is wrong ? I am using Mysql 5.1 on windows

John Woo · Accepted Answer · 2013-05-06 09:32:50Z

2

try joining the table against a subquery which calculates the total val for every ID.

UPDATE  summ 
        INNER JOIN 
        (
            SELECT  id, SUM(VAL) totalVal
            FROM    det
            GROUP   BY id
        ) det ON summ.id = det.id 
SET     summ.val = det.totalVal

answered May 6, 2013 at 9:32

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Biju Soman Over a year ago

yes its working with subquery ...I have already tested that ... But what wrong with this one ??

John Woo Over a year ago

the update only run once for every ID even if it has joined multiple records -- reason to have only 10. summ.val = summ.val+ det.val -- this doesn't perform running total.

Himanshu Over a year ago

Hi John. I have doubt can you solve it? What is an equivalent of GROUP_CONCAT in this situation? It gives expected result in MySQL. How can we achieve that in SQL Server?

John Woo Over a year ago

@hims056 try using MAX() or MIN(). I'll be going home now. be back later. :)

Himanshu Over a year ago

@JW웃 - Is MAX and MIN are actually valid for situation? Or there is any other function like STUFF Reply me when you get time :)

user2045846 · Accepted Answer · 2013-05-08 06:51:24Z

0

Your going about it the log way. Try replacing lines 14 and 15 with this: update summ

set summ.val = (select sum(det.vat) from det where det.id = summ.id);

Or do you really need the temp tables.

Take a look into group by and sum.

answered May 8, 2013 at 6:51

user2045846

Collectives™ on Stack Overflow

MySQL Update existing value with Inner join

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related