Ask the Expert

Getting the autonumber value

How do you get the autonumber after an insert?

Requires Free Membership to View

The easiest way to get the AUTO_INCREMENT value produced by an INSERT statement is to use the LAST_INSERT_ID() function.

Here's an example:

INSERT INTO mytable(col1, col2) VALUES(NULL, 'This is a test');

#We insert a NULL into col1 as in this example col1 is an AUTO_INCREMENT column.

INSERT INTO myOtherTable(colA, colB) VALUES(LAST_INSERT_ID(), 'Another Test');
This will insert the value that was auto-generated into the second table. LAST_INSERT_ID() is connection-specific, meaning that you do not have to worry about other users making INSERTs before you can retrieve the value. It will always return the last AUTO_INCREMENT value generated by your session.

For More Information

This was first published in May 2004

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: