[akina][1]: You need to emulate `ROW_NUMBER()`. There are 2 general ways:

1. Subquery which uses 2 table references (see, for example, [Order by is ignored if inside a derived table of a update][2], with the correction that the subquery must be moved to main query's `UPDATE` clause); and
2. Subquery which uses user-defined variables (see, for example, [query to replace text with sequential numbers][3]).


  [1]: https://dba.stackexchange.com/users/150107/akina
  [2]: https://dba.stackexchange.com/a/244355/150107
  [3]: https://dba.stackexchange.com/a/225999/150107