Get Autoincrement value after INSERT query in MySQL
by Mohan[ Edit ] 2012-09-17 16:56:54
Lot of time we have requirement to update two tables simultaneously. Say for example, we have two tables CUSTOMER_DETAILS and CUSTOMER_ADDRESS. While adding a row in CUSTOMER_DETAILS, few details like address is first written in CUSTOMER_ADDRESS and its reference is added in CUSTOMER_DETAILS table. Now if ADD_ID is a primary key in CUSTOMER_ADDRESS table and if it is an auto increment field than how to add this key as a foreign key in CUSTOMER_DETAILS table?
Well, check following queries:
INSERT INTO CUSTOMER_ADDRESS (ADD_ID, ADD_TEXT) VALUES(NULL, 'some address value');
INSERT INTO CUSTOMER_DETAILS (NAME, ADD_ID, GENDER, PHONE_NO)
VALUES ('James Bond', LAST_INSERT_ID(), 'MALE', 007);
Now when the first query will get executed, address details will be added in CUSTOMER_ADDRESS table and the ADD_ID will be updated based on autoincrement field as we passed NULL in its place. Just after the completion of first query, we want to add custormer details in CUSTOMER_DETAILS table where we will need the ADD_ID that we just added in CUSTOMER_ADDRESS. Now note that we have used LAST_INSERT_ID() function to retrieve the latest autoincrement ID that was used in CUSTOMER_ADDRESS table.
Thus, LAST_INSERT_ID() can be used to fetch latest autoincrement id being used in any INSERT query that was just got executed.
Also, you can fetch the recently added autoincrement ID by using following SELECT query:
SELECT LAST_INSERT_ID();
