Syntax error while looping through all rows of a table in a stored procedure

Question

In the accepted answer to this question How can I loop through all rows of a table? (MySQL) the following code was posted:

INSERT INTO table_B(ID, VAL) VALUES(ID, VAL) FROM table_A LIMIT i,1;

I want to select from table_A into variables first to allow me to reuse it like this:

SELECT VAL FROM table_A INTO variableVal LIMIT i, 1;
INSERT INTO table_B(ID, VAL) VALUES(ID, variableVal);

but that gives me a syntax 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 'LIMIT i, 1;

Here is the code in full

DROP PROCEDURE IF EXISTS ROWPERROW;
DELIMITER ;;
CREATE PROCEDURE ROWPERROW()
BEGIN
  DECLARE i INT DEFAULT 0;
  DECLARE factionCount INT DEFAULT 0;
  DECLARE factionName varchar(100);
  SELECT COUNT(*) FROM faction INTO factionCount;

  SET i = 0;
  WHILE i < factionCount DO   
    SELECT name FROM faction INTO factionName LIMIT i, 1;
    //SELECT name FROM faction LIMIT i, 1 INTO factionName; (doesn't work either)

    INSERT INTO groups_group(name) values (factionName);

    //going to do something else with factionName
    SET i = i + 1;
  END WHILE;
End;
;;

call ROWPERROW();

What do you want to do with `factionName`? Most likely you can do it without loops and cursors. — peterm, Dec 14 '13 at 19:59

peterm · Accepted Answer · 2013-12-14T21:58:09.523

Your immediate error is caused by fact that your SELECT INTO syntax is wrong. INTO clause should go before FROM.

A syntactically correct version of your procedure might look like

DELIMITER $$
CREATE PROCEDURE ROWPERROW()
BEGIN
  DECLARE i INT DEFAULT 0;
  DECLARE factionCount INT DEFAULT 0;
  DECLARE factionName varchar(100);
  SELECT COUNT(*) INTO factionCount FROM faction ;

  SET i = 0;
  WHILE i < factionCount DO   
    SELECT name INTO factionName FROM faction LIMIT i, 1;
    INSERT INTO groups_group(name) VALUES (factionName);

    -- going to do something else with factionName
    SET i = i + 1;
  END WHILE;
END$$
DELIMITER ;

Here is SQLFiddle demo

Now even though it's technically possible and working I strongly discourage you from processing your data that way.

Don't use LOOP at all. If another session delete a few rows while your procedure is working your code will break.
If you want row per row processing use a cursor at least.
If you can express your processing with data set approach (and in most cases you can) stay away from cursors.

A version with a cursor might look like

DELIMITER$$
CREATE PROCEDURE ROWPERROW2()
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE factionName varchar(100);
  DECLARE cursor1 CURSOR FOR SELECT name FROM faction;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cursor1;

  read_loop: LOOP
    FETCH cursor1 INTO factionName;
    IF done THEN
        LEAVE read_loop;
    END IF;
    INSERT INTO groups_group(name) VALUES (factionName);

    -- going to do something else with factionName
  END LOOP;

  CLOSE cursor1;
END$$
DELIMITER ;

Here is SQLFiddle demo

The first solution didn't work, think it may be because of MySQL version as someone else said? I'll look into cursors, performance isn't an issue as it's just going to be a once off thing. It's for people upgrading the version of a Minecraft plugin while the server is stopped. — Jonathan, Dec 14 '13 at 20:58
What version of MySQL are you on? And like I said you don't want to use `LOOP` anyway. It's not about performance it's more about common sense. Try the working example with cursors that I provided or just explain what is your actual goal if you need help with a set-based approach. — peterm, Dec 14 '13 at 21:52
It was helpful but it made me realize loops and cursors where overkill. I found I could do what I wanted in a series of queries. Thanks — Jonathan, Dec 16 '13 at 11:32

score 1 · Answer 2 · answered Dec 14 '13 at 19:51

1

You cannot use variables with LIMIT. To loop through the rows use CURSOR instead.

Try to see if you use SQL's set based approach (using normal SQL statements). Using loops / cursors should be a last resort as you usually get much better performance using normal SQL commands.

answered Dec 14 '13 at 19:51

slaakso

8,331
2
16
27

You **can** use variables (both stored block-level and user(session)-level, in `LIMIT` clause. – peterm Dec 14 '13 at 20:25
This depends on the MySQL version. Limit accepts variable only from MySQL 5.5.6. Based on the error message Jonny is running older version. – slaakso Dec 14 '13 at 20:45

score 0 · Answer 3 · answered Dec 14 '13 at 19:20

0

You need to put @ before variables.

answered Dec 14 '13 at 19:20

Algebar

9
3

@'s give me syntax errors, but I don't think I need to put them in there seeing as it can work without them. Don't think a lack of @'s are what's causing the current syntax error? – Jonathan Dec 14 '13 at 19:35

Syntax error while looping through all rows of a table in a stored procedure

3 Answers3