Trying to update a table in mysql 8.0 using ROW_NUMBER fails within the subquery

I am trying to update a table in my database using ROW_NUMBER in mysql.

Table stats:

id  value           value_rank
-------------------------------
1   635550348443    NULL
2   615342703162    NULL
3   968101508609    NULL
4   976724336509    NULL
5   978154497567    NULL

I tried using the ROW_NUMBER alone as:

SELECT id, value, ROW_NUMBER() OVER w AS value_rank FROM stats WINDOW w AS (ORDER BY value DESC)

and got the following result:

id  value           value_rank
-------------------------------
5   978154497567    1
4   976724336509    2
3   968101508609    3
1   635550348443    4
2   615342703162    5

Then, I tried to update the value_rank using this statement:

UPDATE stats
(SELECT id, value, ROW_NUMBER() OVER w AS rank FROM stats WINDOW w AS (ORDER BY value DESC)) as ranks 
SET stats.value_rank = ranks.rank
WHERE stats.id = ranks.id;

However, I got the following error:

Error in query (1064): Syntax error near ‘(SELECT id, value, ROW_NUMBER() OVER w AS rank FROM stats WINDOW w AS (O’ at line 2

I used this query after following this documentation (last paragraph)

What am I doing wrong?

Answer

You need JOIN:

UPDATE stats s JOIN
       (SELECT id, value, ROW_NUMBER() OVER w AS rank
        FROM stats
        WINDOW w AS (ORDER BY value DESC)
       ) r
       ON s.id = r.id 
    SET s.value_rank = t.rank;

Advertisement

Answer