last_insert_id

Return the most recently auto-generated rowid value

Syntax

last_insert_id(["table_name"])

Arguments

"table_name"

Specifies the name of the table for which its last rowid value is to be retrieved.

Description

This function returns the value of the most recently auto-generated rowid. If "table_name" is specified, then the value returned will be the most recently auto-generated rowid value for that particular table. Otherwise, the most recently auto-generated rowid value regardless of the table is returned.

If no auto-generated value is available (e.g., the table does not contain a rowid primary key column) status errNOTINSERTID is returned.

This function is designed for use with the insert statement so that after a row has been inserted for a table that contains a rowid primary key column, the rows for any referencing tables (i.e., those tables that have a foreign key that references it) can be inserted with the referencing column value being assigned to last_insert_id().

Example

create database acctsdb;
create table acctmgr(
    mgrid       rowid primary key,
    name        char(24),
    hire_date   date,
    commission  double
);

create table patron(
    patid       char(3) primary key,
    name        char(30),
    city        char(17),
    state       char(2),
    mgrid       rowid references acctmgr
);
commit;
insert into acctmgr values ,"Sam Smith",@"2010-04-01",0.05;
insert into patron values "IBM","IBM Corp","Boca Raton","FL",last_insert_id();
commit;