title: WHILE syntax-error in MySQL
body: <p>I'm trying to use a while loop in a one-off query on a MySQL (<code>5.1.41-3ubuntu12.10-log</code>) database:</p>
  
  <pre><code>WHILE ((SELECT COUNT(*) FROM 
      (SELECT id, COUNT(*) AS cnt
          FROM foo
          GROUP BY id
          ORDER BY COUNT(*) DESC) cnts
      WHERE cnt &gt; 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 &gt; 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;     
  </code></pre>
  
  <p>What this does is <em>while there are multiple records with the same id, update one of them with the next id.</em></p>
  
  <p>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:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>I realize this might not be <em>The Right Way</em> 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.</p>
  
  <p>Thanks, </p>
  
  <p>Robin</p>