#1221 - Incorrect usage of UPDATE and ORDER BY

Question

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

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

Answer 1

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

Quoting From MySQL Documentation:

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.

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

---

Read first few paragraphs http://dev.mysql.com/doc/refman/5.7/en/update.html

Answer 2

The @1000111's answer doesn't work. I don't know the reason but MySQL just ignored the ORDER BY in the subquery and updated with the default order (by the Primary_key). A silly solution is wrapping the subquery inside another subquery to create a temporary table.

UPDATE user u 
INNER JOIN 
(
    SELECT * FROM (
        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 t1
) AS t
ON u.<Primary_key> = t.<Primary_key>
SET u.user_rank_planets = t.row_number