Mysql Stored Procedure Error

Asked

Viewed 111 times

0

I have a table of links that I need to delete every month to include new data. I made a stored process to automate this process. This table has about 3 million rows in a monthly period. This can increase or decrease a little each month.

I did the following code below, but Mysql is returning an error that I am unable to identify.

The mistake:

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_links:loop SET @lines := (select id from links Where id >= @min' at line 9

The line 9:

@delete_links: loop

If anyone 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;
mysql sql
  • 4

    At first glance, I think you need to take the @delete_links.

  • 1

    you could expect @Edgarmunizberlinck to post the reply and accept his reply.

  • I didn’t see his answer! I found out by googling! I would have perfectly waited. After I posted there I saw the answer...

  • @Cristianopires can mark your answer as the solution. I don’t care about the points, I just want to help the community. ;)

1 answer

3

I solved the problem.

This line: @delete_ligacoes: loop cannot have the arroba@ 'in the loop. I removed the arroba and worked perfectly.

Thank you!

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.