UPDATE SCORES AS S
SET RANKING = SUB.RANK
FROM(SELECT STUDENT_CODE,SUBJECT_CODE,RANK()OVER(PARTITION BY SUBJECT_CODE ORDER BY SCORE DESC) AS RANK FROM SCORES) AS SUB
WHERE S.STUDENT_CODE = SUB.STUDENT_CODE AND S.SUBJECT_CODE = SUB.SUBJECT_CODE;