update scores as s
set ranking = sub.ranking
from 
    (SELECT student_code, subject_code, RANK() OVER(PARTITION BY subject_code ORDER BY score DESC) as ranking
    FROM scores) as sub
where s.student_code = sub.student_code and s.subject_code = sub.subject_code;

--select * from scores;