How to get last updated ID in SQL?

Article

Mihoko Iijima · Apr 7, 2023 1m read

#ODBC #SQL #Caché #Ensemble #HealthShare #InterSystems IRIS #InterSystems IRIS for Health

InterSystems FAQ rubric

The ID of the last updated record can be obtained using the SQL function LAST_IDENTITY().
* This function can be used with embedded SQL or ODBC but not with Dynamic SQL, SQL Shell, or the Management Portal's SQL interface.

Here's an example of usage with simple Embedded SQL:

Class Test.SQL
{

ClassMethod GetLastID() As %Integer [ SqlProc ]
{
 //There is a table that does not exist when compiling the source code, so compile at runtime
 #SQLCompile  Mode=Deferred
 //Creating a table
 &sql( CREATE  TABLE  Sample.Students(
   StudentName  VARCHAR ( 30 ),
   StudentAge  INTEGER ,
   StudentID  IDENTITY ) )
 //Data registration
 &sql(INSERT  INTO  Sample.Students( StudentName, StudentAge ) values ('Student 1' ,16))
 &sql(INSERT  INTO  Sample.Students( StudentName , StudentAge ) values ('Student 2' ,17))
 // Get the last updated ID
 &sql(SELECT  LAST_IDENTITY() into :lastid  from  Sample.Students)
 return lastid
}

}ObjectScript
ObjectScript

Here is the result of running it as a stored procedure (in the Management Portal):

Please refer to the following documentation page for details.
LAST_IDENTITY【IRIS】
LAST_IDENTITY

Go to the original post written by @Mihoko Iijima

Joel Solon · Apr 10, 2023

LAST_IDENTITY() returns the ID of the last record inserted, updated, or deleted. This is very useful from ODBC/JDBC. From within an ObjectScript method, you can access this directly instead of running an additional SELECT statement:

For Embedded SQL, you can use the %ROWID variable.
For Dynamic SQL, you can use the resultset.%ROWID property.

1 0

Dmitry Maslennikov · Apr 10, 2023

Some scenarios may require the ability to insert a new row with IDENTITY not default, and in this case you would need to use ALLOWIDENTITYINSERT = 1

CREATE TABLE (..) WITH %CLASSPARAMETER ALLOWIDENTITYINSERT = 1;

Some additional details here

2 0