UPDATE scores as sc
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 sc.student_code = sub.student_code
AND   sc.subject_code = sub.subject_code