update total_scores as a
set ranking = b.ranking
from
(
select
    student_code
    ,rank() over(order by total_score desc) ranking
from
    total_scores
) as b
where a.student_code = b.student_code
;

--select * from total_scores order by ranking;