CODEWITHSUNDEEP
mysqlsql-updaterank
Jerem Lachkar
Jerem Lachkar

Reputation: 1197

MySQL 8 : RANK function in UPDATE query impossible?

I have a table 'photos', each photo has a grade, out of ten points : 

-- ID -- fileName -- grade -- last_rank_computed
   1     fn.jpg      6        NULL
   2     fn2.jpg     1        NULL
   3     fn3.jpg     2.5      NULL
   4     fn4.jpg     NULL     NULL

I need to fill last_rank_computed field by computing the grade rank over all the photos (where grade != null). The result would be : 

-- ID -- fileName -- grade -- last_rank_computed
   1     fn.jpg      6        1
   2     fn2.jpg     1        3
   3     fn3.jpg     2.5      2
   4     fn4.jpg     NULL     NULL

I tried this query : 

update photos 
set last_rank_computed = RANK() OVER ( ORDER BY grade desc )  
where grade_avg is not null

But MySQL says :

You cannot use the window function 'rank' in this context.

I could do it directly in PHP by fetching all rows with their respective ranks (because it works in SELECT query), and do N UPDATE queries, but I guess it will be bad for performence. If I have 50000 photos to process, maybe it is more effective with MySQL, in term of performence (time and memory), but please tell me if i'm wrong.

Upvotes: 0

Views: 691

Answers (1)

forpas
forpas

Reputation: 164089

You can do it with a self join to the computed column:

update photos p
inner join (
  select *,
    rank() over (order by grade desc) as rn
  from photos
  where grade is not null
) pp on pp.id = p.id
set p.last_rank_computed = pp.rn;

See the demo.
Results:

| id  | filename | grade | last_rank_computed |
| --- | -------- | ----- | ------------------ |
| 1   | fn.jpg   | 6     | 1                  |
| 2   | fn2.jpg  | 1     | 3                  |
| 3   | fn3.jpg  | 2.5   | 2                  |
| 4   | fn4.jpg  |       |                    |

Upvotes: 2

Related Questions