SQL Server 2016

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE students
    ([studentid] int, [studentscore] int, [rank] int)
;
    
INSERT INTO students
    ([studentid], [studentscore], [rank])
VALUES
    (1, 77, 6),
    (2, 80, 4),
    (3, 90, 2),
    (4, 94, 1),
    (5, 78, 5),
    (6, 88, 3)
;
6 rows affected

SELECT *
FROM students


studentid
studentscore
rank


1
77
6

2
80
4

3
90
2

4
94
1

5
78
5

6
88
3


UPDATE students
SET studentscore = 78 
WHERE studentid = 1
1 rows affected

UPDATE students
SET studentscore = 77
WHERE studentid = 5
1 rows affected

WITH ranks AS (
  SELECT studentid, RANK() OVER (ORDER BY studentscore DESC) AS [rank]
  FROM students
)
UPDATE s
SET s.[rank] = r.[rank]
FROM students s
JOIN ranks r ON s.studentid = r.studentid
6 rows affected

SELECT *
FROM students


studentid
studentscore
rank


1
78
5

2
80
4

3
90
2

4
94
1

5
77
6

6
88
3