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

Hello,

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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.18 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.