使用ORDER BY DESC排名，但更新列

Question

Hey I have a ladder ranking with this data:

Ignore the wins, loses, lvl.

The problem is, on the team table I have a column for the ranking, the ranking I'm using on the table on the image is just a i variable that starts in 0 and increments 1 when finishes the while cyclo. How do I update the ranking column with the real ranking ordered by Points?

Answer 1

You can do this with an update/join:

update rankings r join
       (select r.id, @rn := @rn + 1 as ranking
        from rankings r cross join
             (select @rn := 0) vars
        order by points desc
       ) rp
       on r.id = rp.id
    set r.ranking = rp.ranking;

The subquery uses variables to calculating the ranking.