UPDATE scores
SET ranking = A.rank
FROM(
SELECT RANK() OVER(PARTITION BY subject_code ORDER BY score DESC) AS rank
FROM scores
)AS A;