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;