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;
Learn how to code with Treehouse
- Learn projects with access to 1000+ videos
- Practice live with our Code Challenge Engine
- Get help in our members-only forums
Join the weekly newsletter
Sign up for our newsletter. You'll be updated on new tutorials as they come available.
Subscribe today and get a 50% discount code to Paulund