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

SELECT * FROM SCORES;