#1221-UPDATE和ORDER BY的用法不正确 [英] #1221 - Incorrect usage of UPDATE and ORDER BY

问题描述

要绕过我在其他线程中发布的问题.我尝试了这样的sql语句:

To bypass a problem I posted in a other thread. I tried an sql statement like this:

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子句，该语句可以正常工作. 是否有人知道该问题的解决方案?

I got Error #1221. Without the order by clause, the statement works fine. Is there someone who knows a solution for this issue?

推荐答案

在有多个表的情况下，不能在更新语句中使用order by和limit.

You cannot use order by and limit in update statement in the case of multiple tables.

从MySQL文档报价:

Quoting From MySQL Documentation:

对于多表语法，UPDATE会更新每个名为的表中的行 在满足条件的table_references中.每个匹配的行是 更新一次，即使它多次符合条件.为了 多表语法，不能使用ORDER BY和LIMIT.

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.

Note: Replace u.Primary_key and t.primary_key by the primary key of user table.

阅读前几段 http://dev.mysql.com/doc/refman/5.7/en/update.html

这篇关于#1221-UPDATE和ORDER BY的用法不正确的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！