update total_scores as t
   set ranking=ranktbl.rank
   from (select RANK() OVER(ORDER BY total_score DESC) as RANK
               ,t1.test_code
               ,t1.student_code
           from total_scores t1
        ) ranktbl
where t.test_code=ranktbl.test_code
  and t.student_code=ranktbl.student_code
;