Geek asks and answers

Mysql: update the most recent record field

I am trying to update the last record where the name is John( Johnhas several records but different ID), but I seem to be attached. What happened to my request?

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?

+12
source share
3 answers

You can join both and upgrade 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'

No condition WHERE. It will update the column is_unreadfor the last record.

+7
source

You can try using ORDERand LIMIT.

Try the following:

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

( ) ReceiveTime ( ) ReceiveTime. LIMIT, ReceiveTime.

+36

I get the wrong syntax near ORDER ... why?

0
source

More articles:


All Articles