· Apr 7 1m read

How to get last updated ID in SQL?

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


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

Please refer to the following documentation page for details.

Discussion (2)1
Log in or sign up to continue