Over a million developers have joined DZone.

Update Row With Highest ID In MySQL

· Java Zone

What every Java engineer should know about microservices: Reactive Microservices Architecture.  Brought to you in partnership with Lightbend.

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;


Microservices for Java, explained. Revitalize your legacy systems (and your career) with Reactive Microservices Architecture, a free O'Reilly book. Brought to you in partnership with Lightbend.

Topics:

Published at DZone with permission of Paul Underwood, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

SEE AN EXAMPLE
Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.
Subscribe

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}