Ignoramus12411 wrote:
Wouldn't the first row in the results set be number 1, so the formula would
be (max + current_row_in_resultset())
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 ...
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
Ignoramus12411 wrote:
Wouldn't the first row in the results set be number 1, so the formula would
be (max + current_row_in_resultset())
Paul Lautman wrote:
>
> Wouldn't the first row in the results set be number 1, so the formula
> would be (max + current_row_in_resultset())[/ref]
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.
On Thu, 4 Jan 2007 20:58:07 -0000, Paul Lautman <com> wrote:
>
> Wouldn't the first row in the results set be number 1, so the formula would
> be (max + current_row_in_resultset())
>
>[/ref]
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
Ignoramus30651 wrote:
> >
> > Wouldn't the first row in the results set be number 1, so the formula would
> > be (max + current_row_in_resultset())
> >
> >[/ref]
>
> 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[/ref]
So what about the solution that I have also posted for you?
On Thu, 4 Jan 2007 21:12:25 -0000, Paul Lautman <com> wrote:
>>
>> Wouldn't the first row in the results set be number 1, so the formula
>> would be (max + current_row_in_resultset())[/ref]
>
> 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.
>
>[/ref]
Paul, your answer is FANTASTIC and works VERY WELL!!!
Thank you SO much!
You are da man!!!
i
Bookmarks