SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Addict amy.damnit's Avatar
    Join Date
    Sep 2009
    Posts
    336
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How to Insert Child Record??

    Hi all. Say, I'm very rusty on my DML.

    I have a 1-to-M relationship and need to INSERT a record into the Child Table and point it back to the Parent table.

    How do I do that with SQL and since it requires two queries - presumably - do I need a Transaction??

    Thanks,


    Amy

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    it requires only one query -- the INSERT statement

    you will already know which parent the child belongs to, yes?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict amy.damnit's Avatar
    Join Date
    Sep 2009
    Posts
    336
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    it requires only one query -- the INSERT statement

    you will already know which parent the child belongs to, yes?
    See, I told you I was rusty!! (Plus I posted that on 3 1/2 hours sleep!)

    You are correct, I guess it isn't that difficult.

    Thanks,


    Amy

  4. #4
    SitePoint Addict amy.damnit's Avatar
    Join Date
    Sep 2009
    Posts
    336
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I take back what I said the other day...

    This is as tricky as I originally thought!

    I would like to know how to INSERT both a Parent and a Child record at the same time and be certain that the new Primary Key (i.e. AutoIncrement) and the Foreign Key (Int) match up and that there aren't any conflicts between other users or records?!

    I assume this should be done in a transaction...

    I am Googling "Last_Insert_ID" but could use some help here!

    Thanks,


    Amy

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    1. run INSERT for parent row
    2. use LAST_INSERT_ID to pick up the auto_increment value
    3. run INSERT for child row
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •