WHILE syntax-error in MySQL

Question

I'm trying to use a while loop in a one-off query on a MySQL (5.1.41-3ubuntu12.10-log) database:

WHILE ((SELECT COUNT(*) FROM 
    (SELECT id, COUNT(*) AS cnt
        FROM foo
        GROUP BY id
        ORDER BY COUNT(*) DESC) cnts
    WHERE cnt > 1) != 0) DO
BEGIN

SET @curr_id = (SELECT id FROM 
            (SELECT id, COUNT(*) AS cnt
                FROM foo
                GROUP BY id
                ORDER BY COUNT(*) DESC) cnts
            WHERE cnt > 1
            LIMIT 1);


SET @new_id = (SELECT MAX(id) + 1
        FROM foo);

UPDATE foo 
    SET id = @new_id 
    WHERE id = @curr_id 
    LIMIT 1;

END WHILE;

What this does is while there are multiple records with the same id, update one of them with the next id.

The syntax in the body is correct and the predicate used in the while statement also executes without any trouble on it's own. MySQL returns a syntax error on the beginning of the query:

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 'WHILE ((SELECT count(*) FROM 
    (SELECT id, COUNT(*) AS cnt
        FROM stock_produ' at line 1

I realize this might not be The Right Way of doing things, but this is a very badly (or rather not-at-all) thought out database, so it would be awesome if I could get it to work this way.

Thanks,

Robin

Johan · Accepted Answer · 2011-10-13 12:39:22Z

10

It looks as though you are trying to run this procedural code as an anonymous block. While this works in some databases (like Oracle) it can't be done in MySQL.

If you want to run this then put it in a stored procedure and then call the procedure. Hence:

Create procedure

DELIMITER $$

CREATE PROCEDURE `foo_update_routine`()
BEGIN
  WHILE ((SELECT COUNT(*) FROM 
    (SELECT id, COUNT(*) AS cnt
      FROM foo
      GROUP BY id
      ORDER BY COUNT(*) DESC
    ) cnts
    WHERE cnt > 1) != 0) 
  DO
    SET @curr_id = (SELECT id FROM 
      (SELECT id, COUNT(*) AS cnt
         FROM foo
         GROUP BY id
         ORDER BY COUNT(*) DESC
      ) cnts
      WHERE cnt > 1
      LIMIT 1);

    SET @new_id = (SELECT MAX(id) + 1 FROM foo);

    UPDATE foo SET id = @new_id 
      WHERE id = @curr_id 
      LIMIT 1;

  END WHILE;
END $$

Call Procedure

CALL `foo_update_routine`;

PS You might want to investigate the HAVING clause for your select statements...

edited Oct 13, 2011 at 12:39

Johan

77.4k28 gold badges205 silver badges346 bronze badges

answered Oct 13, 2011 at 12:35

Tom Mac

9,8633 gold badges32 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

thepandaatemyface Over a year ago

Yes! That's it! Seems kind of stupid that you can't do this without the procedure-call. Also thanks for the having-tip. I didn't know about that.

Collectives™ on Stack Overflow

WHILE syntax-error in MySQL

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related