19.2.6 How to Get the Value of an AUTO_INCREMENT Column in
ODBC
A common problem is how to get the value of an automatically generated ID from an INSERT. With
ODBC, you can do something like this (assuming that auto is an AUTO_INCREMENT field):
INSERT INTO foo (auto,text) VALUES(NULL,'text');
SELECT LAST_INSERT_ID();
Or, if you are just going to insert the ID into another table, you can do this:
INSERT INTO foo (auto,text) VALUES(NULL,'text');
INSERT INTO foo2 (id,text) VALUES(LAST_INSERT_ID(),'text');
See section 19.1.12.3 How to Get the Unique ID for the Last Inserted Row.
For the benefit of some
ODBC applications (at least Delphi and
Access), the following
query can be used to find a newly inserted row:
SELECT * FROM tbl_name WHERE auto IS NULL;