7

How can we use while loops in MySQL? My test script:

BEGIN
    SELECT 0 INTO @n;
    WHILE @n < 10 DO
    SELECT @n;
    SET @n := @n +1;
    END WHILE;
END;

But it has syntax errors. I'm running the loop using the SQLyog client in a standard query window. The syntax errors are of the following form:

Error Code: 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...

I've also tried to use the while loop example provided by https://dev.mysql.com/doc/refman/5.6/en/while.html but it still didn't work.

Which part of the script is wrong? (Using MySQL 5.6.)

I'm trying to make a 6 month moving average of some data so I was hoping that a while loop would be able to append the "new" 6 month average onto the "old" 6 month average through each iteration of the loop.

2
  • @user7388 It sounds like you need a "sliding window" query. While MySQL doesn't (yet) have window functions, you could try a query of the form described here: stackoverflow.com/questions/20921937/… Apr 9, 2014 at 17:17
  • 1
    Could you update your question to describe your table(s) and data and the expected output? Apr 9, 2014 at 17:17

3 Answers 3

14

You can't do a for loop in an SQL editor without a stored procedure. I use MySQL Workbench to make this.

A quick stored procedure should do the job:

DROP PROCEDURE IF EXISTS proc_loop_test;
CREATE PROCEDURE proc_loop_test()
BEGIN
  DECLARE int_val INT DEFAULT 0;
  test_loop : LOOP
    IF (int_val = 10) THEN
      LEAVE test_loop;
    END IF;

    SET int_val = int_val +1;
    SELECT int_val; 
  END LOOP; 
END;
2
  • Is it same as while loop? Can we also use while loop in stored procedure?
    – Billy
    Nov 6, 2020 at 6:20
  • @Billy yes, it applies for while loop as well.
    – oNare
    Apr 19, 2022 at 13:48
6

Just to clarify that "while" loops do work:

It was rightfully said that you need to create a stored procedure (can't run anonymous blocks like in Oracle for instance), and it sometimes don't work well in "generic" SQL editors (as PL/SQL don't always work well either)

You can use MySQL Workbench, or also the command line:

16:54 [test]:> delimiter $$
16:54 [test]:> create procedure testwhile ()
    -> begin
    ->   declare n int;
    ->   set n:=0;
    ->   while n <10 do
    ->     select n;
    ->     set n := n+1;
    ->   end while;
    -> end;
    -> $$
Query OK, 0 rows affected (0.00 sec)
16:54 [test]:> delimiter ;
16:54 [test]:> call testwhile();
[...]

As a side note, @variables work as well but they're session variables. In this case I prefer local stored procedure variables (cant be seen or modified outside the scope of the procedure)

1
  • This script doesn't give me a syntax error, unlike the examples from the MYSQL docs, so thanks. But the result seems to be equivalent to "select 0", and I'd expected "select 9"?
    – rwold
    Oct 13, 2020 at 13:52
1

I encountered an issue with confusing syntax error complaints from mysql workbench, despite copy pasting examples found from official documentation and other stack exchange site answers. If you have a long query that's broken up onto multiple lines (for human readability) then you may need to use a delimiter to make it clearer to the machine about which line breaks are "meaningful".

An example of a multi line query (albeit edited) in a procedure that was syntatically accepted by mysql workbench for me.

DELIMITER //
DROP PROCEDURE IF EXISTS methodLoop;
CREATE PROCEDURE methodLoop(p1 INT)
BEGIN
    label1: LOOP
        SET p1 = p1-1;
        IF p1 > 0 THEN

            SELECT *
            FROM Table1 foo
            LEFT JOIN Table 2 bar ON foo.id = bar.id;

        END IF;

    END LOOP label1;
END//

DELIMITER ;

call methodLoop(10);

https://stackoverflow.com/questions/52412225/mysql-create-insert-procedure-statement-incomplete

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.