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
– tgkprog, Commented May 13, 2014 at 17:09

Neron · Accepted Answer · 2014-05-13 17:14:08Z

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, 2014 at 17:14

Neron

1,5787 gold badges32 silver badges59 bronze badges

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

3 Comments

FYI, this will be incredibly slow. You should use a set-based approach.

Any samples that I can use?

tgkprog · Accepted Answer · 2014-05-13 17:04:30Z

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, 2014 at 17:04

tgkprog

4,7434 gold badges47 silver badges76 bronze badges

Would u please explain a little bit more? I am new in mysql and did not understand this answer...