Assigning indexes to rows in a table

Question

PHP

There is a mySQL database, there is a table with a leaderboard. It has an id field, an account and a position. The user with the ID updates his account, the database must recalculate his position. There are no problems here: I take a player with a minimum score from the players with a higher score than mine, add one to his position and write it down. But at the same time, the positions of players with positions equal to or less than mine are violated. Is there a way to update the position of all players in one request?
PHP is driving the whole thing.

Answer 1

UPDATE `board`
SET `position`=`position`+1
WHERE `position`>='$myPosition' AND `id`<>'$myID'

Although I see no reason to make a separate field for the position. By doing this, you only add trouble to yourself, since you need to synchronize 2 fields, which essentially mean the same thing - the player's place in the ranking. Enough `id` and `scores`. When selecting, do ORDER BY `scores` ASC / DESC and already in PHP put down serial numbers in the course of parsing the query results. If you need a position by a specific `id`, then:

SELECT COUNT(`b`.`id`) AS `position`
FROM `board` AS `b` JOIN `board` AS `my`
ON(`b`.`scores`>`my`.`scores` OR (`b`.`scores`=`my`.`scores` AND `b`.`id`<`my`.`id`))
WHERE `my`.`id`='$myID'

...if with one request, but 2 is better: first select the number of points for a specific `id`, then calculate the position based on the `id` and points.
It is better to increase the complexity of the selection than to update half the table each time.