I am trying to create this procedure in MySQL and show the error mentioned below.
CREATE DEFINER=`root`@`%` PROCEDURE `synctables`()
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
begin
insert into tdest
select s.keyval,s.val1,s.val2,s.val3 from tsource s left join tdest d
on s.keyval = d.keyval where d.keyval is null;
declare finished INTEGER DEFAULT 0;
declare keyv decimal;
declare v1 decimal;
declare v2 decimal;
declare v3 decimal;
DECLARE c1 CURSOR FOR select s.* from tsource s inner join tdest d on s.keyval = d.keyval;
DECLARE CONTINUE HANDLER
FOR NOT FOUND SET finished = 1;
open c1;
getvals: loop
fetch c1 into keyv,v1,v2,v3;
if finished = 1 then
leave getvals;
end if;
update tdest set val1=@v1,val2=@v2,val3=@v3 where keyval=@keyv;
end loop getvals;
close c1;
end
Error de SQL (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near
declare finished INTEGER DEFAULT 0;
declare keyv decimal;
declare v1 decimal;
at line 13 */
When I delete this line the procedure is ok:
insert into tdest
select s.keyval,s.val1,s.val2,s.val3 from tsource s left join tdest d
on s.keyval = d.keyval where d.keyval is null;
Any ideas why the insert into is giving me an error?
UPDATE
statement. And it will run a lot faster.