Skip to content
Related Articles

Related Articles

Improve Article
How to get ID of the last updated row in MySQL?
  • Last Updated : 13 Jun, 2019

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

We should write an update query in such a way that we can get the last updated ID in the update statement itself
The code mentioned below has been created in a generalized sense and can be used easily just by replacing the values in , which is demonstrated below:

CREATING A TABLE


CREATE TABLE 
tbl(Rno INTEGER AUTO_INCREMENT ,
      Name VARCHAR(50) ,
       CONSTRAINT tbl_Rno PRIMARY KEY(Rno));
INSERT INTO tbl (Name) VALUES ('value1');
INSERT INTO tbl (Name) VALUES ('value2');
INSERT INTO tbl (Name) VALUES ('value3');

GETTING THE LAST UPDATED ID
The Logic over here suggests that we first update our last updated ID to 0 since it clears out any function previously performed then we update our values using the where clause/query in SQL and select the last updated ID using the @lastupdatedID query


SET @LastUpdateID := 0;
UPDATE tbl SET Name = 'value_new',Rno = (SELECT @LastUpdateID := Rno) 
   WHERE Name = 'value3';
SELECT @LastUpdateID AS LastUpdateID;

GETTING MULTIPLE LAST UPDATED ID
The Logic over here suggests that we update our values using the where clause/query in SQL and select the last updated ID using the @lastupdatedID query and to select multiple ID’s we use Concat query since it this code starts its searching from the bottom you would get our answer in a descending order as shown in the example.



SET @LastUpdateID = NULL;

UPDATE tbl SET Name = 'changed' WHERE Name 'changed'
AND (SELECT @LastUpdateID := CONCAT_WS(',', Rno, @LastUpdateID));
SELECT @LastUpdateID;

EXAMPLE #1 (For Last Updated ID)

Example #2 (For multiple Last Updated ID’s)

My Personal Notes arrow_drop_up
Recommended Articles
Page :