# LAST\_INSERT\_ID

## Syntax

```sql
LAST_INSERT_ID(), LAST_INSERT_ID(expr)
```

## Description

`LAST_INSERT_ID()` (no arguments) returns the first automatically generated value successfully inserted for an [AUTO\_INCREMENT](/docs/server/reference/data-types/auto_increment.md) column as a result of the most recently executed `INSERT`\
statement. The value of `LAST_INSERT_ID()` remains unchanged if no rows are successfully inserted.

If one gives an argument to `LAST_INSERT_ID()`, then it will return the value of the expression and\
the next call to `LAST_INSERT_ID()` will return the same value. The value is also sent to the client\
and can be accessed by the [mysql\_insert\_id](/docs/connectors/mariadb-connector-c/api-functions/mysql_insert_id.md) function.

For example, after inserting a row that generates an `AUTO_INCREMENT` value, you can get the value like this:

```sql
SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                9 |
+------------------+
```

You can also use `LAST_INSERT_ID()` to delete the last inserted row:

```sql
DELETE FROM product WHERE id = LAST_INSERT_ID();
```

If no rows were successfully inserted, `LAST_INSERT_ID()` returns 0.

You can also use [INSERT...RETURNING](/docs/server/reference/sql-statements/data-manipulation/inserting-loading-data/insertreturning.md) for this purpose.

The value of `LAST_INSERT_ID()` will be consistent across all versions if all rows in the [INSERT](/docs/server/reference/sql-statements/data-manipulation/inserting-loading-data/insert.md) or [UPDATE](/docs/server/reference/sql-statements/data-manipulation/changing-deleting-data/update.md) statement were successful.

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(`<kbd>`expr`</kbd>`)`, the effect is undefined.)

If the previous statement returned an error, the value of `LAST_INSERT_ID()` is undefined. For transactional tables, if the statement is rolled back due to an error, the value of `LAST_INSERT_ID()` is left undefined. For manual [ROLLBACK](/docs/server/reference/sql-statements/transactions/rollback.md), the value of `LAST_INSERT_ID()` is not restored to that before the transaction; it remains as it was at the point of the `ROLLBACK`.

Within the body of a stored routine (procedure or function) or a trigger, the value of `LAST_INSERT_ID()` changes the same way as for statements executed outside the body of these kinds of objects. The\
effect of a stored routine or trigger upon the value of `LAST_INSERT_ID()` that is seen by following statements depends on the kind of routine:

* If a [stored procedure](/docs/server/server-usage/stored-routines/stored-procedures.md) executes statements that change the value of `LAST_INSERT_ID()`, the new value will be seen by statements that follow the procedure call.
* For [stored functions](/docs/server/server-usage/stored-routines/stored-functions.md) and [triggers](/docs/server/server-usage/triggers-events/triggers.md) that change the value, the value is restored when the function or trigger ends, so following statements will not see a changed value.

## Examples

```sql
CREATE TABLE t (
  id INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
  f VARCHAR(1)) 
ENGINE = InnoDB;

INSERT INTO t(f) VALUES('a');

SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                1 |
+------------------+

INSERT INTO t(f) VALUES('b');

INSERT INTO t(f) VALUES('c');

SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                3 |
+------------------+

INSERT INTO t(f) VALUES('d'),('e');

SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                4 |
+------------------+

SELECT * FROM t;
+----+------+
| id | f    |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
|  4 | d    |
|  5 | e    |
+----+------+

SELECT LAST_INSERT_ID(12);
+--------------------+
| LAST_INSERT_ID(12) |
+--------------------+
|                 12 |
+--------------------+

SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|               12 |
+------------------+

INSERT INTO t(f) VALUES('f');

SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                6 |
+------------------+

SELECT * FROM t;
+----+------+
| id | f    |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
|  4 | d    |
|  5 | e    |
|  6 | f    |
+----+------+

SELECT LAST_INSERT_ID(12);
+--------------------+
| LAST_INSERT_ID(12) |
+--------------------+
|                 12 |
+--------------------+

INSERT INTO t(f) VALUES('g');

SELECT * FROM t;
+----+------+
| id | f    |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
|  4 | d    |
|  5 | e    |
|  6 | f    |
|  7 | g    |
+----+------+
```

## See Also

* [mysql\_insert\_id](/docs/connectors/mariadb-connector-c/api-functions/mysql_insert_id.md)
* [AUTO\_INCREMENT](/docs/server/reference/data-types/auto_increment.md)
* [AUTO\_INCREMENT handling in InnoDB](/docs/server/server-usage/storage-engines/innodb/auto_increment-handling-in-innodb.md)
* [Sequences](/docs/server/reference/sql-structure/sequences.md) - an alternative to auto\_increment
* [INSERT...RETURNING](/docs/server/reference/sql-statements/data-manipulation/inserting-loading-data/insertreturning.md)

<sub>*This page is licensed: GPLv2, originally from*</sub> [<sub>*fill\_help\_tables.sql*</sub>](https://github.com/MariaDB/server/blob/main/scripts/fill_help_tables.sql)

{% @marketo/form formId="4316" %}


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://mariadb.com/docs/server/reference/sql-functions/secondary-functions/information-functions/last_insert_id.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.