Mysql: Update field of most latest record

Question

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?

score 72 · Answer 1 · answered Mar 30 '13 at 07:02

72

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.

answered Mar 30 '13 at 07:02

Aiias

4,683
1
18
34

@enchance - Not a problem, just wanted to make sure my answer was still relevant to your question :). – Aiias Mar 30 '13 at 07:04

score 8 · Accepted Answer · edited Aug 14 '20 at 16:36

8

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.

edited Aug 14 '20 at 16:36

HoldOffHunger

18,769
10
104
133

answered Mar 30 '13 at 07:02

John Woo

258,903
69
498
492

Sorry, I'm getting an `Error Code : 1064`. – enchance Mar 30 '13 at 07:19
`Query : update messages s1 set is_unread=1 inner join (SELECT Name, MAX(ReceiveTime) max_time FROM messages group by Messa... Error Code : 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'inner join (SELECT Name, MAX(ReceiveTime) max_time FROM messages group' at line 2` – enchance Mar 30 '13 at 07:22
Brilliant! Do you mind explaining why the other one didn't work? I uncommented the `WHERE` so I can target specific rows and it still worked. :) – enchance Mar 30 '13 at 07:42
@enchance which other one? – John Woo Mar 30 '13 at 07:43
The first code before you edited it and came up with this one. – enchance Mar 30 '13 at 20:14
Oh, it was really a synrax error because the SET statement was brfore the INNER JOIN keyword. – John Woo Mar 30 '13 at 20:53

Mysql: Update field of most latest record

2 Answers2

Linked