update scores as sc set ranking = sub.ranking
from 
(
SELECT test_code,student_code,subject_code,RANK() OVER(PARTITION BY subject_code ORDER BY score DESC) as ranking
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;

select * from scores