UPDATE scores SET ranking = rnk
FROM (
SELECT student_code, subject_code, rank() over (partition by subject_code order by score desc  ) as rnk
FROM scores) sub_rnk
WHERE scores.student_code = sub_rnk.student_code
AND scores.subject_code = sub_rnk.subject_code