Getting an error while running MySQL loop

Question

I'm trying to run a mysql loop in phpMyAdmin in order to add price records for a number of products at the same time. When running this query I get the following error:

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 '$$' at line 11

Any idea why this would be?

DELIMITER $$
CREATE PROCEDURE doiterate()
BEGIN
DECLARE p1 INT Default 0;
  label1: LOOP
    SET p1 = p1 + 1;
    IF p1 < 44 THEN
        INSERT INTO `price`(`product`, `site`, `amount`)
        VALUES (p1,'10','12.99');
    END IF;
  END LOOP label1;
END $$

Abhik Chakraborty · Answer 1 · 2015-03-26T18:13:42.620

0

You have some syntax issue and need to end the loop before the final end.

DELIMITER $$
CREATE PROCEDURE doiterate()
BEGIN
DECLARE p1 INT Default 0;
  label1: LOOP
    if p1 = 44 then 
      LEAVE  label1;
    end if;

    INSERT INTO `price`
     (`product`, `site`, `amount`)
     VALUES 
     (p1,'10','12.99');
    SET p1 = p1 + 1;
  END LOOP; 
END$$

Here is a test in mysql cli

mysql> DELIMITER $$
mysql> CREATE PROCEDURE doiterate()
    -> BEGIN
    -> DECLARE p1 INT Default 0;
    ->   label1: LOOP
    ->     if p1 = 44 then 
    ->       LEAVE  label1;
    ->     end if;
    -> 
    ->     INSERT INTO `price`
    ->      (`product`, `site`, `amount`)
    ->      VALUES 
    ->      (p1,'10','12.99');
    ->     SET p1 = p1 + 1;
    ->   END LOOP; 
    -> END$$
Query OK, 0 rows affected (0.00 sec)

edited Mar 26 '15 at 18:13

answered Mar 26 '15 at 18:05

Abhik Chakraborty

44,654
6
52
63

thanks, however I'm now getting #1308 - LEAVE with no matching label: loop_label – jamiemax Mar 26 '15 at 18:09
Sorry I had a typo fixed it. – Abhik Chakraborty Mar 26 '15 at 18:10
i'm now getting the same error as before - syntax error at line 15 – jamiemax Mar 26 '15 at 18:11
I just tested in mysql cli and it works well. Are you trying on mysql cli or somewhere else ? – Abhik Chakraborty Mar 26 '15 at 18:14
Hmm in phpmyadmin I guess you do not need to sepecify delimiter and can set there directly.http://stackoverflow.com/questions/17481890/how-to-write-a-stored-procedure-in-phpmyadmin – Abhik Chakraborty Mar 26 '15 at 18:16
thanks, that works when i run it from the command line but it only creates two records for some reason, i need to create records with product IDs from 1 to 44 – jamiemax Mar 27 '15 at 11:45

Getting an error while running MySQL loop

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 '$$' at line 11

1 Answers1