Update Row With Highest ID In MySQL
Join the DZone community and get the full member experience.Join For Free
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;
Published at DZone with permission of Paul Underwood, DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.