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)
Share your thoughts in the comments
Please Login to comment...