Professional Web Applications Themes

Current row number whenm using INSERT...SELECT? - MySQL

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 ...

  1. #1

    Default Current row number whenm using INSERT...SELECT?

    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 Guest

  2. #2

    Default Re: Current row number whenm using INSERT...SELECT?

    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 Guest

  3. #3

    Default Re: Current row number whenm using INSERT...SELECT?

    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.


    Paul Guest

  4. #4

    Default Re: Current row number whenm using INSERT...SELECT?

    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 Guest

  5. #5

    Default Re: Current row number whenm using INSERT...SELECT?


    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?

    Captain Guest

  6. #6

    Default Re: Current row number whenm using INSERT...SELECT?

    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
    Ignoramus30651 Guest

Similar Threads

  1. Current Page Number
    By Kimberly_G@adobeforums.com in forum Adobe Acrobat SDK
    Replies: 5
    Last Post: April 1st, 10:04 PM
  2. What's faster - loop for insert or insert...select.
    By mr. modus in forum Coldfusion Database Access
    Replies: 2
    Last Post: December 10th, 01:57 AM
  3. Replies: 1
    Last Post: October 15th, 12:56 AM
  4. printing current frame number... but.. :( ?
    By dddddd in forum Macromedia Flash
    Replies: 0
    Last Post: August 26th, 11:28 PM
  5. Insert Current Url ??
    By John Kavanagh in forum Macromedia Dreamweaver
    Replies: 1
    Last Post: July 22nd, 03:54 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139