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