mysql - UPDATE 和 ORDER BY 的使用不正确。比它可以替换为？

Question

UPDATE PlayerLeague l 
JOIN PlayerStat s ON l.player_id=s.player_id 
SET l.league_id=8 
WHERE l.league_id=2 
ORDER BY s.score 
DESC LIMIT 5

我有错误:

{ [Error: ER_WRONG_USAGE: Incorrect usage of UPDATE and ORDER BY] 
code: 'ER_WRONG_USAGE', index: 0 }

这是什么意思？

我试试这个:

UPDATE PlayerLeague l 
SET l.league_id=8 
WHERE l.player_id IN 
(SELECT player_id FROM PlayerStat s 
JOIN PlayerLeague l ON s.player_id=l.player_id 
WHERE l.league_id=2 
ORDER BY s.score 
DESC LIMIT 5)

但这也是错误的。

Answer 1

我找到了解决方案:

UPDATE PlayerLeague l 
JOIN (SELECT s.player_id 
    FROM PlayerStat s 
    JOIN PlayerLeague l 
    ON s.player_id=l.player_id 
    WHERE l.league_id=2 
    ORDER BY s.score DESC LIMIT 5
) AS temp
ON l.player_id=temp.player_id
SET l.league_id=8