Mysql:更新最新记录的字段 [英] Mysql: Update field of most latest record

问题描述

我正在尝试更新名称为 John 的最新记录(John 有多个记录但 ID 不同)，但我似乎处于绑定状态.我的查询有什么问题?

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?

推荐答案

可以同时加入，根据条件进行更新.

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'

没有 WHERE 条件.它将全部更新列 is_unread 以获取最新条目.

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

这篇关于Mysql:更新最新记录的字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！