SQL Zone is brought to you in partnership with:

Paulund is a website dedicated to writing tutorials and code snippets about Web Development, the main subjects are PHP, Wordpress, jQuery, CSS3 and HTML5. Paul is a DZone MVB and is not an employee of DZone and has posted 124 posts at DZone. You can read more from them at their website. View Full User Profile

Update Row With Highest ID In MySQL

04.23.2014
| 2940 views |
  • submit to reddit

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, author and DZone MVB. (source)

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)