While exists in mysql

Question

How should i use while loop in mysql?

while exists (select * from table1) 
Do
 //sql statements
end while

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

This what I want to do:

1. Inserting 20K records into a table2 from table1.
2. Looping table1 and inserting to table 3 the first 50 records. Deleting table1's 50 records with joining table3.
3. End loop when 20K records are deleted from table1.

Any help/thoughts?

Answer 1

You might try making sure there is a semicolon after your END WHILE Or alternatively try putting a BEGIN and END; around your statements.

However, we really should examine a set-based method.

EDIT: If, as in your comments, you are determined to do this in 50 row batches.... for whatever reason... You may try selecting the rowcount, then dividing it by your batch size to get an interation count variable.

For example, if you have 20,000 rows and you are inserting them in batched of 50 then you know that you will need 400 iterations. Then you can use an int and do WHILE X < 400 This will be faster than doing EXISTS queries.

Untested code (probably not valid syntax either, but should give you the idea)

INSERT INTO table2 (columns)
SELECT columns from table1

DECLARE @TopCount int DEFAULT 0;
DECLARE @CurrCount int DEFAULT 0;
SELECT @TopCount = COUNT(*) from table2

DO WHILE (@CurrCount < @TopCount)
 BEGIN    
  INSERT INTO table3 (columns)
  SELECT columns from table2 LIMIT 50
  DELETE table2 LIMIT 50
  SET @CurrCount = @CurrCount + 50
 END;
END WHILE;

This is pretty silly, imho, but I still do not understand your goal.