Mysql - Insert values into multiple tables with unknown primary key

Question

What would be the best practice in the following situation:

I want to write a single MySQL statement to take data from a result set (join of 3 tables), and insert those values into 3 new tables. There are now 2 problems, the one is that I want to insert into 3 tables at the same time, while the other is that one of the values that needs to go into 2 of the three tables is a primary kry generated by the inserting values into the first of the three tables.

In high-level terms (ignore syntax), I want to:

INSERT INTO    `table1`(
                   `one_field1`,
                   `one_field2`,
                   ...
               ), `table2`( -- not possible?
                   `two_field1`,
                   `two_field2`,
                   ...
               ), `table3`( -- not possible?
                   `three_field1`,
                   `three_field2`,
                   ...
               )
SELECT         value1,
               value2,
               ...,
               value3,
               value4,
               ...,
               value5,
               value6,
               ...
FROM           ...

where value3 and value5 is the primary key generated from inserting value1 and value2 into table1.

Is it better (best-practice) to rather split this into multiple queries, or is there an effective way of doing this in a single query.

Thanks

sreimer sreimer · Accepted Answer · 2011-03-30T13:12:12

Inserting into multiple tables with one statement is not possible http://dev.mysql.com/doc/refman/5.1/en/insert.html

You can use `last_insert_id() to get the auto-increment value from the last insert. So if you break up your inserts, you can get the last primary key for each (assuming you are using auto increments) before running the next insert. http://dev.mysql.com/doc/refman/5.1/en/information-functions.html#function_last-insert-id

Mysql - Insert values into multiple tables with unknown primary key

2 Answers