MySQL错误: You can't specify target table 'users_words' for update in FROM clause

Question

这个问题已经有答案了:

MySQL Error 1093 - Can't specify target table for update in FROM clause (16 个回答)

已关闭 9 年前。

请看一下我的 MySQL 子句。

UPDATE `users_words` SET `priority` = (SELECT MAX(`priority`)+1 FROM `users_words`) where `userid` = 89 AND `wordid`="agree"

这里我要更新priority字段的现有最大值 priority 加 1字段值。但我收到错误 You can't specify target table 'users_words' for update in FROM clause .

如何解决这个问题？

Answer 1

只需将子查询包装在另一个 SELECT 中:

UPDATE `users_words` 
SET `priority` = (SELECT x.* FROM (SELECT MAX(`priority`)+1 FROM `users_words`) x) 
WHERE `userid` = 89 AND `wordid`="agree"