mysql update order_mysql – #1221 – UPDATE和ORDER BY的使用不正确

为了绕过我在另一个帖子中发布的问题.我试过像这样的sql语句：

UPDATE user u JOIN (SELECT @i := 0) r

SET user_rank_planets = (@i := (@i + 1))

WHERE user_active=1

ORDER BY user_planets DESC

我收到错误#1221.没有order by子句,该语句可以正常工作.

是否有人知道这个问题的解决方案？

解决方法:

在多个表的情况下,您不能在update语句中使用order by和limit.

引用MySQL文档：

For the multiple-table syntax, UPDATE updates rows in each table named

in table_references that satisfy the conditions. Each matching row is

updated once, even if it matches the conditions multiple times. For

multiple-table syntax, ORDER BY and LIMIT cannot be used.

UPDATE user u

INNER JOIN

(

SELECT

*,

(@i := (@i + 1)) AS row_number

FROM user u

CROSS JOIN (SELECT @i := 0) r

WHERE user_active=1

ORDER BY user_planets DESC

)AS t

ON u.Primary_key = t.primary_key

SET u.user_rank_planets = t.row_number.

注意：用user表的主键替换u.Primary_key和t.primary_key.

标签：mysql,join

来源： https://codeday.me/bug/20190519/1134510.html