update scores as s
   set ranking=ranktbl.ranking 
 from (select RANK() OVER(PARTITION BY test_code, subject_code ORDER BY score DESC) as ranking
             , test_code
             , student_code
             , subject_code
             , score 
         from scores 
      ) as ranktbl
where s.test_code=ranktbl.test_code
  and s.student_code=ranktbl.student_code
  and s.subject_code=ranktbl.subject_code