Update Row With Highest ID In MySQL
Recently needed to update the last inserted row of a table but didn't have anyway in knowing what the highest ID in the table was.
I can easily do this by using the max() function to select the highest ID in the table.
SELECT MAX(id) FROM table;
Then I can use the result of this query in the UPDATE query to edit the record with the highest ID. But this is quite a easy query so I should be able to do this in one query by using a nested select query on the UPDATE.
UPDATE table SET name='test_name' WHERE id = (SELECT max(id) FROM table)
But the problem with this is that the MAX() function doesn't work inside a nested select so had to find another way of doing this.
I found out that you can use an ORDER BY and a LIMIT in an UPDATE query therefore I can use a combination of these in the UPDATE query to make sure I only update the record with the highest ID, by doing a descendant order on the ID and limiting the return to only 1 record.
UPDATE table SET name='test_name' ORDER BY id DESC LIMIT 1;
(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)