I have the following table structure for a table Player
Table Player {
Long playerID;
Long points;
Long rank;
}
Assuming that the playerID and the points have valid values, can I update the rank for all the players based on the number of points in a single query? If two people have the same number of points, they should tie for the rank.
I'm using hibernate, so cannot execute any queries with variables, so I came up with this query which is very inefficient. Can this be optimized to work with the constraints specified above?
update player g1
set g1.rank = 1 +
((SELECT count(*) from
(select * from player) g2
where g2.points > g1.points))