SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Zealot Pozor's Avatar
    Join Date
    Apr 2004
    Location
    Switzerland
    Posts
    114
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Auto_increment Problem

    Hi,

    i try to insert a row , but the problem is i want to insert the autoincrement value
    to another filed in the inserted row. And this with only one Insert query.

    Last_ID() doesnt work because it retrieve the previous id not the current one.
    I've tried something like
    INSERT INTO table SET ... , fieldname = autoincrementfieldname

    but that doesn't work.

    Any suggestion how i can achieve this with only one query?

    The solutions with two queries i know already well. -> Dont suggest that plz.

    Greez Pozor

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Pozor
    ... i want to insert the autoincrement value
    to another filed in the inserted row.
    could you give an example please?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot Pozor's Avatar
    Join Date
    Apr 2004
    Location
    Switzerland
    Posts
    114
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,
    assume a table like
    table
    id_a //autoincrement
    somefield
    id_b

    Now i wanna insert a row:

    INSERT INTO table SET somefield = 'sometext', id_b = id_a
    that doesn work.

    i wanna have the same value in id_b as in id_a which is an autoincrement field.

    do you know how it could be achieved? or is it in mysql impossible?

    Greez Pozor

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    my advice is not to have the id_b colulmn at all

    if for some reason you need to pull two values the same, do this --
    Code:
    select id_a
         , somefield
         , id_a   as id_b
      from yourtable
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot Pozor's Avatar
    Join Date
    Apr 2004
    Location
    Switzerland
    Posts
    114
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hi,

    you cant understand without knowing the context. sure i wouldn't fill in every row
    the same value into two fields. Its just one of many different cases, and in one of
    them must be the value of the autoincerment also be filled into another field.

    greez Stefan

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    sorry, i was just trying to save you some heartache

    any use of an auto_increment for anything other than to provide uniqueness is a sure recipe for trouble

    but if you insist, there is basically only one way to do it

    declare id_b as integer NULL

    load your data, and leave id_b alone (let it be NULL for new rows)

    then run this query:
    Code:
    update yourtable
       set id_b = id_a
     where id_b is null
    you will have to do this after every insert

    you're right, i cannot imagine any reason why you would want this, but that would be the way to get it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Zealot Pozor's Avatar
    Join Date
    Apr 2004
    Location
    Switzerland
    Posts
    114
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hi,
    thats the way i do it, when i need the the same autoincrement value in another field.
    Just a note: i only need this in 5% of all cases, in all other cases i do know the value
    before.

    I was curious if it is posssible to get all the data into my row with one query.

    Thank you anyway for your thoughts

    Greez Pozor


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
  •