56,687
社区成员
发帖
与我相关
我的任务
分享
delimiter $$
drop procedure if exists updateTotalTravels$$
create procedure updateTotalTravels()
begin
declare _done int default 0;
declare ref_id varchar(32);
declare ref_count int;
declare _cur1 cursor for select id from ca_travel_method where is_system=false;
declare continue handler for sqlstate '02000' set _done = 1;
open _cur1;
repeat
fetch _cur1 into ref_id;
set ref_count=(select count(*) from ca_travel ct join ca_travel_method ctm on ct.travel_method_id=ctm.id where ctm.id=ref_id and ct.status=2);
update ca_travel_method set total_travels=ref_count where id=ref_id;
until _done end repeat;
close _cur1;
end$$
delimiter ;
call updateTotalSuccessfulPayments();