I inserted record into a table with the help of Cursor and loop after closing cursor i wrote update statement to update some record which inserted by cursor so reords not updating. but i ran query outside then records updating
my try is drop procedure if exists kpsf_inopdormantac;
create procedure kpsf_inopdormantac(inp_branchcode varchar(6), inp_date date)
begin declare c_branchcode varchar(6);
declare c_accounttype varchar(4);
declare c_accountsubtype varchar(6);
declare c_accountno varchar(12);
declare c_lasttxndate date;
declare c_inopdate date;
declare c_temp varchar(25);
declare c1 cursor for SELECT
branchcode, accounttype, accountsubtype, accountno, lasttxndate, inoperativedate FROM accountmaster WHERE branchcode = inp_branchcode AND accounttype in ('10','11') AND accountstatus!='C' AND lasttxndate < ( SELECT date_add(inp_date,interval -24 month));
truncate table inopdormantac;
commit;
open c1;
loop1:loop fetch c1 into c_branchcode, c_accounttype, c_accountsubtype, c_accountno, c_lasttxndate, c_inopdate;
INSERT into inopdormantac (branchcode, accounttype, accountsubtype, accountno, acstatus, lasttxndate, inopdate)
VALUES
(c_branchcode, c_accounttype, c_accountsubtype, c_accountno, 'InOperative', c_lasttxndate, c_inopdate);
end loop loop1;
close c1;
set c_temp='InActive';
update inopdormantac SET acstatus = c_temp WHERE lasttxndate < ( SELECT
date_add('2015-10-06',interval -120 month));
end;
plz help me
