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 FreeRecently 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;
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 }}