CODEWITHSUNDEEP
mysqlsqlsql-update
enchance
enchance

Reputation: 30471

Mysql: Update field of most latest record

I'm trying to update the latest record where name is John (John has multiple records but different ID) but I seem to be in a bind. What's wrong with my query?

UPDATE messages_tbl SET is_unread=1
WHERE ReceiveTime = (SELECT MAX(ReceiveTime) FROM messages_tbl WHERE name='John')

Is there a better way to do something like this?

Upvotes: 25

Views: 35541

Answers (2)

John Woo
John Woo

Reputation: 263813

You can join both and perform update based on the condition.

UPDATE  messages a
        INNER JOIN
        (
            SELECT  name , MAX(ReceiveTime) max_time
            FROM    messages 
            GROUP   BY name 
        ) b ON  a.name = b.name AND
                a.ReceiveTime = b.max_time
SET     a.is_unread = 1
-- WHERE    a.name = 'John'

Without the WHERE condition. It will all update the column is_unread for the latest entry.

Upvotes: 8

Aiias
Aiias

Reputation: 4748

You could try using ORDER and LIMIT.

Try this:

UPDATE messages_tbl SET is_unread = 1
WHERE name = 'John'
ORDER BY ReceiveTime DESC
LIMIT 1

This query will update the rows in order of the highest (most recent) ReceiveTime to the lowest (oldest) ReceiveTime. Used in conjunction with LIMIT, only the most recent ReceiveTime will be altered.

Upvotes: 74

Related Questions