MySQL Loop Variable in a Query

Question

I want to use the loop index "i" in the result of my select statement in order to insert it into another table. Is is like:

set i=0;
while i<25 do

    insert into a (x,y,z)
    select a,b,i
    from table1;

    set i= i+1;
end while;

What is the way to do it?

Do you want to insert all rows of table1 ? is there some where condition ? maybe you can post a real sample. — tgkprog, May 13 '14 at 17:09

score 2 · Accepted Answer · answered May 13 '14 at 17:14

2

DOne :)

I have just created variable i as @i and it is all solved.

Like this:

set @i=0;
while @i<25 do

    insert into a (x,y,z)
    select a,b,@i
    from table1;

    set @i= @i+1;
end while;

thx anyway :)

answered May 13 '14 at 17:14

Neron

FYI, this will be incredibly slow. You should use a set-based approach. – Kermit May 13 '14 at 17:16
Any samples that I can use? – Neron May 13 '14 at 17:18
1

Yes, [create a numbers table](http://stackoverflow.com/a/187410/679449) then join to it. – Kermit May 13 '14 at 17:19

score 0 · Answer 2 · answered May 13 '14 at 17:04

0

Open the table separately asa cursor, put fields in to variables, then use those variables and i to insert data in to the table with a

insert into a (x,y,z) values (var1, var2, i).

What you have written would put multiple rows in to a table a with each value of i.

answered May 13 '14 at 17:04

tgkprog

Would u please explain a little bit more? I am new in mysql and did not understand this answer... – Neron May 13 '14 at 17:06

2 Answers2