Using INSERT..SELECT with MySQL

I have been working on a small application at home lately which is going to use MySQL for data persistence.  I was messing around with my tables tonight and got into a situation where I wanted to add data into one table using the last inserted value from the auto_increment primary key field in another table.

According to the documentation for MySQL, I should be able to use LAST_INSERT_ID(), but I couldn't figure out exactly how to get it to pull the last inserted ID from a different table.

So, I then tried the following code...

INSERT INTO table2 (id, field1, field2) SELECT NULL, 4, id FROM table1 t WHERE t.id IS NULL;

According to some information that I found in a comment posted on the documentation for the INSERT..SELECT command on the MySQL documentation site, this should give me the last inserted ID from the first table.  Nope... this results in no errors and no rows being added to the table.  Very weird!

Finally, I came up with the following...

INSERT INTO table2 (id, field1, field2) SELECT NULL, 4, MAX(id) from table1;

So far, this seems to give me the results that I am after.

Technorati Tags: MySQL