CODEWITHSUNDEEP
mysqlsql
user3803112
user3803112

Reputation: 103

Getting the last inserted ID with expression in mysql

I want to get the value of the last id insert in a table. How i can do this in mysql? For eg : in db2 we have

SET var_ID = (SELECT ID FROM NEW TABLE (INSERT INTO val_CLIENT(E_VER, NAME, TYPE) VALUES(0, val_NAME, 502)));

The above statement needs to be converted into mysql. How can i do this?

Upvotes: 1

Views: 3486

Answers (3)

kchason
kchason

Reputation: 2885

You can use the LAST_INSERT_ID() function.

Do your insert statement:

INSERT INTO val_CLIENT(E_VER, NAME, TYPE) VALUES(0, val_NAME, 502);

Depending if you're doing it in a stored procedure, you will have to modify this, but if you're looking to select it.

SELECT LAST_INSERT_ID() AS `ID`;

To store it as a variable, you can use the SET statement.

SET @VarID = (SELECT LAST_INSERT_ID());

Upvotes: 3

SALEH
SALEH

Reputation: 1562

If your ID column is of type AUTO_INCREMENT, Use LAST_INSERT_ID() after the INSERT statement

SELECT LAST_INSERT_ID() as ID

However, for concurrent requests using same connection, this will lead into inconsistent result. In that case, the following query is a safe bet:

SELECT ID FROM val_CLIENT 
ORDER BY ID DESC 
LIMIT 1

Upvotes: 2

Regis
Regis

Reputation: 55

A possible query:

SELECT id FROM tableORDER BY id DESC LIMIT 1

Upvotes: 1

Related Questions