theharls theharls - 1 month ago 6
MySQL Question

MySQL INSERT using SELECT with field that increments with each inserted row

I have two tables with columns that look like this:

Table_1:


key | value
1 | apple
2 | banana
3 | carrot


Table_2:

sub_index | value


Using a stored procedure, I want to insert rows from Table_1 into Table_2 using INSERT...SELECT such that for every time I execute the query,
sub_index
starts from 1.

For example, after I first run the query

INSERT INTO TABLE_2 (value) SELECT value FROM TABLE_1 WHERE key=2


Table_2 should look like this:

sub_index | value
1 | banana


and then if I run another query

INSERT INTO TABLE_2 (value) SELECT value FROM TABLE_1 WHERE key < 4


Table_2 will now look like this:

sub_index | value
1 | banana
1 | apple
2 | banana
3 | carrot


and if I run a further query

INSERT INTO TABLE_2 (value) SELECT value FROM TABLE_1 WHERE key <= 2


Table_2 will now look like this:

sub_index | value
1 | banana
1 | apple
2 | banana
3 | carrot
1 | apple
2 | banana


In other words, every time I run the query, I want the
sub_index
field to start from 1 and increment for every record inserted by the query.

How do I implement this behaviour for
sub_index
using only SQL? (ie. no programming with PHP.)

Any advice gratefully received!

Answer
 set @row_nb=0;
 INSERT INTO Table_2 (`sub_index`,`value`) select (@row_nb := @row_nb +1) as `key`,`value` from Table_1 WHERE `key`< 4