CODEWITHSUNDEEP
mysql
Alex
Alex

Reputation: 2122

Using LAST_INSERT_ID() within a multi row insert

Is it possible to use LAST_INSERT_ID() within an insert query?

INSERT INTO mytable (col1, col2) VALUES ('val1', null), ('val2', LAST_INSERT_ID())

Trying the above LAST_INSERT_ID() returns 0, Just seeing if i can combine the query.

Upvotes: 1

Views: 371

Answers (2)

Russell Gallop
Russell Gallop

Reputation: 1699

I think that this explains it well (http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id):

"The currently executing statement does not affect the value of LAST_INSERT_ID(). Suppose that you generate an AUTO_INCREMENT value with one statement, and then refer to LAST_INSERT_ID() in a multiple-row INSERT statement that inserts rows into a table with its own AUTO_INCREMENT column. The value of LAST_INSERT_ID() will remain stable in the second statement; its value for the second and later rows is not affected by the earlier row insertions. (However, if you mix references to LAST_INSERT_ID() and LAST_INSERT_ID(expr), the effect is undefined.) "

Upvotes: 0

Alex
Alex

Reputation: 2122

From what i found it won't work as LAST_INSERT_ID doesn't have a value until the whole statement has finished.

Upvotes: 3

Related Questions