Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Update Row With Highest ID In MySQL

DZone's Guide to

Update Row With Highest ID In MySQL

· Java Zone
Free Resource

Just released, a free O’Reilly book on Reactive Microsystems: The Evolution of Microservices at Scale. 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;


Strategies and techniques for building scalable and resilient microservices to refactor a monolithic application step-by-step, 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 DZONE NEWSLETTER

Dev Resources & Solutions Straight to Your Inbox

Thanks for subscribing!

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

X

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

{{ parent.tldr }}

{{ parent.urlSource.name }}