Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL Loop Variable in a Query

Tags:

loops

sql

mysql

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?

like image 479
Neron Avatar asked May 13 '14 16:05

Neron


People also ask

How do I run a query in a MySQL loop?

The simplest possible looping construct is the LOOP statement. The syntax for this statement is as follows: [ label :] LOOP statements END LOOP [ label ]; The statements between the LOOP and END LOOP statements will be repeated indefinitely, until the LOOP is terminated.

Can we write loop in MySQL?

LOOP implements a simple loop construct, enabling repeated execution of the statement list, which consists of one or more statements, each terminated by a semicolon ( ; ) statement delimiter. The statements within the loop are repeated until the loop is terminated. Usually, this is accomplished with a LEAVE statement.

Is there a foreach in MySQL?

foreach() acts on server side only, and does not require shell access nor the mysql command line client, although it may be spawned from within the mysql client. foreach() accepts several types of collections. They are automatically recognized by their pattern.


1 Answers

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 :)

like image 107
Neron Avatar answered Nov 02 '22 23:11

Neron