mysql - 用最小值sql更新行

Question

我有这张表:

-----------------------
summonerId | timestamp
-----------------------
253222     | 14395235091096
929112     | 14395235091056
(...)

我想更新具有较低时间戳的行，但是当我这样做时我不能

UPDATE summoners_shell 
SET 
summonerId = ".$s.",
timestamp = ".$time." 
WHERE timestamp = (SELECT MIN(timestamp))

它更新所有行!为什么？我该如何做我想做的事？

Answer 1

当 SELECT-subquery 在 WHERE-clause 中时，它会锁定表，因此更新无法通过。只需使用 JOIN 即可

UPDATE summoners_shell AS s1
JOIN (SELECT MIN(timestamp) AS mt FROM summoners_shell) AS mt ON (s1.timestamp = mt.mt)
SET 
    summonerId = ".$s.",
    timestamp = ".$time."