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