How Can I Get the ID of the Last Updated Row in MySQL?

Finding the ID of the Last Updated Row in MySQL

Accessing the ID of the last updated row in MySQL can be achieved using a concise and efficient technique.

To accomplish this, execute the following SQL query:

SET @update_id := 0;
UPDATE some_table SET column_name = 'value', id = (SELECT @update_id := id)
WHERE some_other_column = 'blah' LIMIT 1;
SELECT @update_id;

This query accomplishes the task by setting a user variable @update_id to 0 and subsequently updating the targeted row in the some_table table. During the update process, the ID of the row being modified is assigned to @update_id. Finally, the value of the variable is retrieved using the last SELECT statement, providing the ID of the last updated row.

Expanding the Technique

The aforementioned approach can be extended to retrieve the IDs of all affected rows during an update operation:

SET @uids := null;
UPDATE footable
   SET foo = 'bar'
 WHERE fooid > 5
   AND ( SELECT @uids := CONCAT_WS(',', fooid, @uids) );
SELECT @uids;

This modified query creates a string by concatenating the IDs of all rows that satisfy the update criteria, separated by commas. Executing this query returns the IDs of all updated rows as a comma-separated string.

The above is the detailed content of How Can I Get the ID of the Last Updated Row in MySQL?. For more information, please follow other related articles on the PHP Chinese website!