Mysql：更新最新记录的字段
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15715922/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Mysql: Update field of most latest record
提问by enchance
I'm trying to update the latest record where name is John(Johnhas multiple records but different ID) but I seem to be in a bind. What's wrong with my query?
我正在尝试更新 name 所在的最新记录John（John有多个记录但 ID 不同），但我似乎陷入困境。我的查询有什么问题？
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?
有没有更好的方法来做这样的事情？
采纳答案by John Woo
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 WHEREcondition. It will all update the the column is_unreadfor the latest entry.
如果没有WHERE条件。它将全部更新is_unread最新条目的列。
回答by Aiias
You could try using ORDERand LIMIT.
您可以尝试使用ORDER和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) ReceiveTimeto the lowest (oldest) ReceiveTime. Used in conjunction with LIMIT, only the most recent ReceiveTimewill be altered.
此查询将按从最高（最近）ReceiveTime到最低（最旧）的顺序更新行ReceiveTime。与 结合使用LIMIT，只会ReceiveTime更改最近的。
