Current row number whenm using INSERT...SELECT?

#1

We have an app that, for certain legacy reasons, does not use
AUTO_INCREMENT feature.

We have a table with id column that is supposed to be auto
incremented, and need to insert data there via INSERT...SELECT
statement.

@max = SELECT MAX( id ) from mytable
INSERT INTO mytable SELECT (@max + current_row_in_resultset() + 1, foo, bar)

what I would like is to find a function that I tentatively named
current_row_in_resultset, that returns number of current row in a
resultset. First row would have a number of (say) 0, the next row 1, etc.

Am I clear? Is there such a function?

i

#2

Ignoramus12411 wrote:

Quote:

We have an app that, for certain legacy reasons, does not use
AUTO_INCREMENT feature.

We have a table with id column that is supposed to be auto
incremented, and need to insert data there via INSERT...SELECT
statement.

@max = SELECT MAX( id ) from mytable
INSERT INTO mytable SELECT (@max + current_row_in_resultset() + 1,
foo, bar)

what I would like is to find a function that I tentatively named
current_row_in_resultset, that returns number of current row in a
resultset. First row would have a number of (say) 0, the next row 1,
etc.

Am I clear? Is there such a function?

i

Wouldn't the first row in the results set be number 1, so the formula would
be (@max + current_row_in_resultset())

#3

Paul Lautman wrote:

Quote:

Ignoramus12411 wrote:
We have an app that, for certain legacy reasons, does not use
AUTO_INCREMENT feature.

We have a table with id column that is supposed to be auto
incremented, and need to insert data there via INSERT...SELECT
statement.

@max = SELECT MAX( id ) from mytable
INSERT INTO mytable SELECT (@max + current_row_in_resultset() + 1,
foo, bar)

what I would like is to find a function that I tentatively named
current_row_in_resultset, that returns number of current row in a
resultset. First row would have a number of (say) 0, the next row 1,
etc.

Am I clear? Is there such a function?

i

Wouldn't the first row in the results set be number 1, so the formula
would be (@max + current_row_in_resultset())

Oh and I forgot to give you the answer!

SELECT @max:=MAX(id) from mytable;
INSERT INTO mytable SELECT @max:=@max+1, foo, bar FROM foobartable

Although the actual INSERT statement is untested.

#4

On Thu, 4 Jan 2007 20:58:07 -0000, Paul Lautman <paul.lautman (AT) btinternet (DOT) com> wrote:

Quote:

Ignoramus12411 wrote:
We have an app that, for certain legacy reasons, does not use
AUTO_INCREMENT feature.

We have a table with id column that is supposed to be auto
incremented, and need to insert data there via INSERT...SELECT
statement.

@max = SELECT MAX( id ) from mytable
INSERT INTO mytable SELECT (@max + current_row_in_resultset() + 1,
foo, bar)

what I would like is to find a function that I tentatively named
current_row_in_resultset, that returns number of current row in a
resultset. First row would have a number of (say) 0, the next row 1,
etc.

Am I clear? Is there such a function?

i

Wouldn't the first row in the results set be number 1, so the formula would
be (@max + current_row_in_resultset())

I have no idea what such a function would start with, 1 or 0, and I do
not care as long as it is incremented for every row.

i

#5

Ignoramus30651 wrote:

Quote:

On Thu, 4 Jan 2007 20:58:07 -0000, Paul Lautman <paul.lautman (AT) btinternet (DOT) com> wrote:
Ignoramus12411 wrote:
We have an app that, for certain legacy reasons, does not use
AUTO_INCREMENT feature.

We have a table with id column that is supposed to be auto
incremented, and need to insert data there via INSERT...SELECT
statement.

@max = SELECT MAX( id ) from mytable
INSERT INTO mytable SELECT (@max + current_row_in_resultset() + 1,
foo, bar)

what I would like is to find a function that I tentatively named
current_row_in_resultset, that returns number of current row in a
resultset. First row would have a number of (say) 0, the next row 1,
etc.

Am I clear? Is there such a function?

i

Wouldn't the first row in the results set be number 1, so the formula would
be (@max + current_row_in_resultset())

I have no idea what such a function would start with, 1 or 0, and I do
not care as long as it is incremented for every row.

i
So what about the solution that I have also posted for you?

#6

On Thu, 4 Jan 2007 21:12:25 -0000, Paul Lautman <paul.lautman (AT) btinternet (DOT) com> wrote:

Quote:

Paul Lautman wrote:
Ignoramus12411 wrote:
We have an app that, for certain legacy reasons, does not use
AUTO_INCREMENT feature.

We have a table with id column that is supposed to be auto
incremented, and need to insert data there via INSERT...SELECT
statement.

@max = SELECT MAX( id ) from mytable
INSERT INTO mytable SELECT (@max + current_row_in_resultset() + 1,
foo, bar)

what I would like is to find a function that I tentatively named
current_row_in_resultset, that returns number of current row in a
resultset. First row would have a number of (say) 0, the next row 1,
etc.

Am I clear? Is there such a function?

i

Wouldn't the first row in the results set be number 1, so the formula
would be (@max + current_row_in_resultset())

Oh and I forgot to give you the answer!

SELECT @max:=MAX(id) from mytable;
INSERT INTO mytable SELECT @max:=@max+1, foo, bar FROM foobartable

Although the actual INSERT statement is untested.

Paul, your answer is FANTASTIC and works VERY WELL!!!

Thank you SO much!

You are da man!!!

i

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Current row number whenm using INSERT...SELECT?

comp.databases.mysql comp.databases.mysql

Current row number whenm using INSERT...SELECT? - 01-04-2007 , 12:02 PM

Re: Current row number whenm using INSERT...SELECT? - 01-04-2007 , 02:58 PM

Re: Current row number whenm using INSERT...SELECT? - 01-04-2007 , 03:12 PM

Re: Current row number whenm using INSERT...SELECT? - 01-05-2007 , 09:39 AM

Re: Current row number whenm using INSERT...SELECT? - 01-05-2007 , 09:51 AM

Re: Current row number whenm using INSERT...SELECT? - 01-05-2007 , 04:15 PM