MySQL: Get ID of the Last Updated Row

In this post, I am sharing a demonstration on how to get last updated ID of the last updated row in MySQL.

Many times, we require updating the data basis on last updated table id.

We should write update query such a way that we can get a last updated ID in the update statement only.

Here, I am sharing different ways for getting a last updated ID of the last updated row.

Let’s first create a table with sample data. 

1 2 3 4 5 6 7 8 9 10

CREATE TABLE tbl_LastUpdateID (     Rno INTEGER AUTO_INCREMENT     ,Name VARCHAR(50)     ,CONSTRAINT pk_tbl_LastUpdateID_Rno PRIMARY KEY(Rno) );   INSERT INTO tbl_LastUpdateID (Name) VALUES ('Anvesh'); INSERT INTO tbl_LastUpdateID (Name) VALUES ('Neevan'); INSERT INTO tbl_LastUpdateID (Name) VALUES ('Roy');

First script to get a last updated ID:

1 2 3 4 5

SET @LastUpdateID := 0; UPDATE tbl_LastUpdateID SET Name = 'Alex',Rno = (SELECT @LastUpdateID := Rno) WHERE Name = 'Roy'; SELECT @LastUpdateID AS LastUpdateID;

Get all last updated ID when more than one row updates:

1 2 3 4 5 6

SET @LastUpdateID := NULL; UPDATE tbl_LastUpdateID    SET Name = 'All' WHERE Name <>'All' AND (SELECT @LastUpdateID := CONCAT_WS(',', Rno, @LastUpdateID)); SELECT @LastUpdateID;

Second script using LAST_INSERT_ID():
Before this, please execute the insert script again.

1 2 3 4 5

UPDATE tbl_LastUpdateID SET Name = 'Martin'     ,Rno=LAST_INSERT_ID(Rno) WHERE Name = 'Roy'; SELECT LAST_INSERT_ID();