I have a connection table that I need to delete every month to include new data. I made a stored procedure to automate this process. This table has about 3 million rows in a monthly period. This may increase or decrease a bit each month.
I made the following code, but Mysql is returning an error that I can not identify.
The error:
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 '@delete_ligacoes: loop SET @lines: = (select id from links where id> = @min' at line 9
Line 9:
@delete_connections: loop
If someone can help me ...
BEGIN
SET @minimo = 0;
@delete_ligacoes: loop
SET @linhas := (select id from ligacoes where id >= @minimo order by id limit 1000, 1);
prepare stmt from @linhas;
execute stmt;
deallocate prepare stmt;
if @linhas is null then
leave @delete_ligacoes;
end if;
SET @queryDelete := (DELETE FROM 'ligacoes' WHERE id >= @minimo AND id < @linhas);
prepare stmt1 from @queryDelete;
execute stmt1;
deallocate prepare stmt1;
SET @minimo = @linhas;
end loop;
SET @varDelete := (DELETE FROM 'ligacoes' WHERE id >= @minimo);
prepare stmt2 from @varDelete;
execute stmt2;
deallocate prepare stmt2;
END;