Update Row With Highest ID In MySQL
Update Row With Highest ID In MySQL
Join the DZone community and get the full member experience.
Join For FreeThe CMS developers love. Open Source, API-first and Enterprise-grade. Try BloomReach CMS 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;
BloomReach CMS: the API-first CMS of the future. Open-source & enterprise-grade. - As a Java developer, you will feel at home using Maven builds and your favorite IDE (e.g. Eclipse or IntelliJ) and continuous integration server (e.g. Jenkins). Manage your Java objects using Spring Framework, write your templates in JSP or Freemarker. Try for free.
Like This Article? Read More From DZone
Published at DZone with permission of Paul Underwood , DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.
{{ parent.title || parent.header.title}}
{{ parent.tldr }}
{{ parent.linkDescription }}
{{ parent.urlSource.name }}