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)