Results 1 to 4 of 4

Thread: Insert identity

  1. #1
    Join Date
    Aug 2003
    Posts
    23

    Unanswered: Insert identity

    Hi to all!

    I must do an insert into statement on a identity indexed table:
    MSG_ID --> the identity (autoincrement)
    MSG_DESCRI
    MSG_PARENT --> the field i must fill

    I must fill MSG_PARENT with a MSG_ID value, after the insert..
    ex:
    insert into MESSAGES (MSG_DESCRI,MSG_PARENT)
    values ('hello world',identity value after insert)..
    what's the best way to do this??

    I must do this cause the first node of my tree must have MSG_PARENT = MSD_ID.. the root node..
    in sql server i use a stored proc, i exec the insert and i use the @@identity sysvar... but my mysql version don't supports stored procedures..
    can anyone help me??

    thx!!!
    Alx_81 =)

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the root node should have parent id set to null

    in mysql, you can use last_insert_id() function to retrieve the value
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2003
    Posts
    23

    ok.. but

    null is not the best for my root node..
    but i can use the function that you tell me in last message..
    so, under the same transaction that funcion don't return any value..
    right? i must wait the commit of my transaction?
    But i must do this in one transaction...
    Alx_81 =)

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    read what the manual says about the function, it explains how to use it

    and yes, null is the best for the root node

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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