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 the column is_unread for the latest entry.

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