update scores as sc
set ranking = sub.rank
from (
select 
    test_code
    ,student_code
    ,subject_code
    ,rank() over (partition by subject_code order by score desc) as rank 
from scores) as sub
where sc.test_code = sub.test_code
and sc.student_code = sub.student_code
and sc.subject_code = sub.subject_code;