# LAST\_INSERT\_ID Returns the last value inserted into an AUTO\_INCREMENT column. ## Syntax ```sql LAST_INSERT_ID() LAST_INSERT_ID(expr) ``` ## Arguments * `expr`: any expression. This may be a column name, the result of another function, or a math operation. ## Return type BIGINT ## Remarks * `LAST_INSERT_ID()` only returns automatically generated `AUTO_INCREMENT` values. The value of `LAST_INSERT_ID()` remains unchanged if you set the `AUTO_INCREMENT` column of a row to an explicit or non-automatically generated value. * `LAST_INSERT_ID` is maintained on a per-connection basis, allowing multiple clients to execute the `UPDATE` statement and generate their own sequence with the `SELECT` statement, without affecting or being affected by the sequence generated by other clients. * After a multi-insert statement, the value returned by `LAST_INSERT_ID()` is the first value inserted in that group of records. * The value of `LAST_INSERT_ID()` remains unchanged if no record is successfully added in an `INSERT` statement. * While using `INSERT IGNORE` if the row is ignored, the value of `LAST_INSERT_ID` remains unchanged. * If the value of `LAST_INSERT_ID` is changed inside a stored procedure, the changed value is seen by statements executed after the procedure call. * If the value of `LAST_INSERT_ID` is changed inside a stored function, the value is restored when the function ends. * If `expr` is specified with `LAST_INSERT_ID`, `LAST_INSERT_ID(expr)` returns the value of the expression **expr** and stores this value as the next value to be returned by `LAST_INSERT_ID()`. * `LAST_INSERT_ID` works with `UPDATE`/`INSERT` statements, but it may not work as expected with `SELECT` statements because of the distributed nature of SingleStore. > **📝 Note**: `LAST_INSERT_ID(expr)` is only updated after `INSERT` and `UPDATE` queries. So, `LAST_INSERT_ID()` cannot be used to retrieve the value for `LAST_INSERT_ID(expr)` after executing other SQL statements, like `SELECT` or `SET` with `LAST_INSERT_ID(expr)`. ## Examples In the following example, `LAST_INSERT_ID()` returns **2** when executed after a multi-insert statement, which is the first inserted value in the statement. ```sql CREATE TABLE persons ( id BIGINT PRIMARY KEY AUTO_INCREMENT, firstname VARCHAR(64), lastname VARCHAR(64)); INSERT INTO persons VALUES (NULL, 'Eponymous', 'Bach'); ``` ```sql SELECT LAST_INSERT_ID(); ``` ```output +------------------+ | LAST_INSERT_ID() | +------------------+ | 1 | +------------------+ ``` ```sql INSERT INTO persons VALUES (NULL, 'Ping', 'Baudot'), (NULL, 'Count', 'Modulo'), (NULL, 'Hugh', 'Rustic'); ``` ```sql SELECT LAST_INSERT_ID(); ``` ```output +------------------+ | LAST_INSERT_ID() | +------------------+ | 2 | +------------------+ 1 row in set (0.00 sec) ``` Following each previous `INSERT` statement, the value of `LAST_INSERT_ID()` was changed since a `NULL` value was inserted in the `AUTO_INCREMENT` column. ```sql INSERT INTO persons VALUES (5, 'Grant', 'Acos'); ``` ```sql SELECT LAST_INSERT_ID(); ``` ```output +------------------+ | LAST_INSERT_ID() | +------------------+ | 2 | +------------------+ ``` The following set of queries demonstrate the behavior of `LAST_INSERT_ID(expr)`. Create a table and initiate the sequence counter as: ```sql CREATE TABLE sequences (sequence_name TEXT PRIMARY KEY, value BIGINT NOT NULL); INSERT INTO sequences VALUES ('my_sequence_name', 0); ``` Now, generate the sequence: ```sql UPDATE sequences SET value = LAST_INSERT_ID(value + 1) WHERE sequence_name = 'my_sequence_name'; SELECT LAST_INSERT_ID(); ``` ```output +------------------+ | LAST_INSERT_ID() | +------------------+ | 1 | +------------------+ ``` The `UPDATE` statement increments the counter. ## Using Stored Procedures to Support Multiple Sequences ```sql CREATE TABLE sequence_generator ( seq_name TEXT PRIMARY KEY, next_val INT NOT NULL ); DELIMITER // CREATE OR REPLACE PROCEDURE create_new_sequence ( sequence_name TEXT NOT NULL, starting_value INT NOT NULL) AS BEGIN INSERT INTO sequence_generator VALUES (sequence_name, starting_value); END // -- The following stored procedure returns a single ID from the sequence generator from a particular instance CREATE OR REPLACE PROCEDURE get_single_sequence_id (sequence_name TEXT NOT NULL) AS BEGIN UPDATE sequence_generator SET next_val = LAST_INSERT_ID(next_val + 1) WHERE seq_name = sequence_name; echo SELECT LAST_INSERT_ID() - 1 AS next_id; END // -- The following procedure reserves multiple IDs from the sequence generator from a particular sequence and returns the first id CREATE OR REPLACE PROCEDURE get_multiple_sequence_id ( sequence_name TEXT NOT NULL, num_ids INT NOT NULL) AS BEGIN UPDATE sequence_generator SET next_val = LAST_INSERT_ID(next_val + num_ids) WHERE seq_name = sequence_name; echo SELECT (LAST_INSERT_ID() - num_ids) AS start_id; END // DELIMITER ; CALL create_new_sequence("my_seq_1", 0); CALL create_new_sequence("my_seq_2", 100); ``` ```sql CALL get_single_sequence_id("my_seq_1"); ``` ```output +---------+ | next_id | +---------+ | 0 | +---------+ ``` ```sql CALL get_single_sequence_id("my_seq_1"); ``` ```output +---------+ | next_id | +---------+ | 1 | +---------+ ``` ```sql CALL get_single_sequence_id("my_seq_2"); ``` ```output +---------+ | next_id | +---------+ | 100 | +---------+ ``` ```sql CALL get_multiple_sequence_id("my_seq_2", 10); ``` ```output +----------+ | start_id | +----------+ | 101 | +----------+ ``` ```sql CALL get_multiple_sequence_id("my_seq_2", 10); ``` ```output +----------+ | start_id | +----------+ | 111 | +----------+ ``` *** Modified at: June 28, 2024 Source: [/db/v9.1/reference/sql-reference/information-functions/last-insert-id/](https://docs.singlestore.com/db/v9.1/reference/sql-reference/information-functions/last-insert-id/) (An index of the documentation is available at /llms.txt)