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