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();

Answer 1

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.

1. Don't use LOOP at all. If another session delete a few rows while your procedure is working your code will break.

2. If you want row per row processing use a cursor at least.

3. 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