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