Resorting a group of rows-CodePudding

Let's say I have the following table, rankings,

|film_id|user_id|rank|
|-------|-------|----|
|1      |1      |2   |
|2      |1      |3   |
|3      |1      |1   |
|1      |2      |2   |
|2      |2      |1   |
|3      |2      |3   |

and I delete film id 2.

Is there a MySQL way to rerank the remaining films per user in a single query, such:

|film_id|user_id|rank|
|-------|-------|----|
|1      |1      |2   |
|3      |1      |1   |
|1      |2      |1   | <- changed from 2 to 1
|3      |2      |2   | <- changed from 3 to 2

Edit for clarity: I'm not sure I would call it a formula, exactly, but all I want to do is have a query that loads the remaining films in order of their rank, and then rebuild the ranking in that same order, and consecutive.

In other words, if after deleting a row, a user's rankings went 1,2,4,5,6, I want it to be rebuilt to be consecutive starting at 1 (1,2,3,4,5).

Thank you!

CodePudding user response：

Join the table to a query that uses ROW_NUMBER() window function to rebuild the rankings:

UPDATE tablename t1
INNER JOIN (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY `rank`) rn
  FROM tablename
) t2 ON t2.film_id = t1.film_id AND t2.user_id = t1.user_id
SET t1.`rank` = t2.rn
WHERE t1.`rank` <> t2.rn; -- actually this in not needed 
                          -- as MySql does not perform the update 
                          -- if the current and new values are the same

See the demo.