MySQL Forums
Forum List  »  Events (Job Scheduler)

MySQL auto update doesn't work
Posted by: joel trab
Date: April 01, 2013 10:36AM

HI,

I'm trying to do an automatically update using MySQL events.

When I use datas from a single table, the update ends well, but when I use datas from other tables, the update doesn't work.

Here is my event code :
"delimiter |

create event test
on schedule every 1 minute
do
begin
declare p,g,b,pp,pg,pb,i,ap,ag,ab,np,ng,nb,ep,eg,eb INT;
declare done INT default 0;
declare c cursor for select IDVILLAGE, PIERRE, GOLD, BOIS, PRODPIERRE, PRODGOLD, PRODBOIS, EPIERRE, EGOLD, EBOIS from VILLAGE;
declare continue handler for sqlstate '02000' set done = 1;
open c;
repeat
fetch c into i,p,g,b,pp,pg,pb,ep,eg,eb;
select ab.NIVEAUAMEL into ap from AMELBAT ab, BATIMENTVILLAGE bv where ab.IDBAT=bv.IDBATVIL and bv.IDVILLAGE=iv and ab.IDAMELIORATION in (79, 82, 85);
select ab.NIVEAUAMEL into ag from AMELBAT ab, BATIMENTVILLAGE bv where ab.IDBAT=bv.IDBATVIL and bv.IDVILLAGE=iv and ab.IDAMELIORATION in (80, 83, 86);
select ab.NIVEAUAMEL into ab from AMELBAT ab, BATIMENTVILLAGE bv where ab.IDBAT=bv.IDBATVIL and bv.IDVILLAGE=iv and ab.IDAMELIORATION in (81, 84, 87);
set p = p+(1+(ap/20))*pp;
set g = g+(1+(ag/20))*pg;
set b = b+(1+(ab/20))*pb;
if p>ep then
set p=ep;
end if;
if g>eg then
set g=eg;
end if;
if b>eb then
set b=eb;
end if;
update VILLAGE set PIERRE=p, BOIS=b, GOLD=g where IDVILLAGE=i;
until done end repeat;
close c;
end |

delimiter ;"

Options: ReplyQuote


Subject
Views
Written By
Posted
MySQL auto update doesn't work
3709
April 01, 2013 10:36AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.