CODEWITHSUNDEEP
mysqlfor-loop
Josef Sábl
Josef Sábl

Reputation: 7742

How to properly do a LOOP in mysql

Ok this might be ridiculous but I've been scratching my head for quite a few minutes now. What is wrong with this syntax?

SET @maxIterations = 10;
SET @i = 0;

myloop: LOOP
    SET @i = @i + 1;

    UPDATE `mytable`
    SET `field` = 'value';

    if @i < @maxIterations THEN ITERATE myloop; END IF;

    LEAVE myloop;
END LOOP myloop;

I am getting error near 'myloop: LOOP SET i = i + 1' at line 1

Upvotes: 1

Views: 144

Answers (1)

Michael Berkowski
Michael Berkowski

Reputation: 270617

Your syntax is generally correct, except that MySQL does not permit flow control structures like IF/ELSE, WHILE, LOOP in anything other than a stored procedure or function ("stored programs"). This limitation is documented here:

MySQL supports the IF, CASE, ITERATE, LEAVE LOOP, WHILE, and REPEAT constructs for flow control within stored programs. It also supports RETURN within stored functions.

You will be able to correctly create this as a stored procedure, as in:

DELIMITER $$
CREATE PROCEDURE myproc()
BEGIN
  SET @maxIterations = 10;
  SET @i = 0;

  myloop: LOOP
      SET @i = @i + 1;

      UPDATE `mytable`
      SET `field` = 'value';

      if @i < @maxIterations THEN ITERATE myloop; END IF;

      LEAVE myloop;
  END LOOP myloop;
END$$
DELIMITER ;

Upvotes: 1

Related Questions