CODEWITHSUNDEEP
mysql
Pash
Pash

Reputation: 382

Mysql update table with select using group by

am using mysql 5.5.9 on windows 7 32 bit guys am really bad condition need desperate help In my procedure i had temporary table temp01 and in my database i had agent table and my stored procedure has IN Ico_id int as parameter along with this i didnt have cl column in temp01 table please take a note of it.

update agent a 
join ( 
    select sum(ifnull(t_dr_amt,0)) - sum(ifnull(t_cr_amt,0)) as cl 
    from temp01 
    group by t_ac_id 
) as tt 
    on a.co_id = tt.t_co_id 
    and a.agent_id = tt.t_ac_id
SET a.cl = tt.cl
where a.co_id = 1 
AND lower(a.io) = 'y';

when i run the stored procedure it give me error : unknown column tt.t_co_id in 'on cluase'

Upvotes: 0

Views: 415

Answers (1)

kitti
kitti

Reputation: 14794

Your subquery only returns one column (cl). You alias this subquery as tt, so there is no tt.t_co_id. If this is a column in temp01, you can change it to this:

select 
    sum(ifnull(t_dr_amt,0)) - sum(ifnull(t_cr_amt,0)) as cl,
    t_co_id,
    t_ac_id
from temp01 
group by t_ac_id

That way you select the t_co_id column as well. I also added the t_ac_id column, since you'll get an error on that one next. ;)

Upvotes: 1

Related Questions