Results 1 to 3 of 3

Thread: Nested Inserts?

  1. #1
    Join Date
    Feb 2004

    Unanswered: Nested Inserts?

    I have table configuration where I need to create a parent record in one table and then create multiple child records in other tables linked by a foreign key.

    when the 1st set of records is created, there is only one child record in each child table, so I'd like to be able to do something like this

    INSERT INTO header SET foo=5, default_child=(INSERT INTO child_table SET bar=9)

    and have the auto increment value for the child_table be returned and inserted into the default_child field. Is there any way to do this?

  2. #2
    Join Date
    Oct 2007
    If your version 0f mysql is late 5.0,
    it is possible with trigger

  3. #3
    Join Date
    Mar 2007
    Do it in a transaction:

    1) Begin transaction
    2) Insert parent record first
    3) Use last_insert_id() to get ID of parent record and SET @parent
    4) Insert child record using parent key
    5) Use last_insert_id() to update parent record with default child.
    6) Insert child record using parent key
    7) Insert child record using parent key
    8) Insert child record using parent key
    last) Close transaction

    If any of the above fails the transaction will rollback.

Posting Permissions

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