UPDATE scores
SET ranking = sub.ran
FROM(
SELECT student_code
    ,subject_code 
    ,RANK() OVER(PARTITION BY subject_code ORDER BY score DESC) AS ran
FROM scores) AS sub
WHERE scores.student_code = sub.student_code
    AND scores.subject_code = sub.subject_code;

--
select *
from scores;