UPDATE scores AS main 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 main.student_code = sub.student_code
	AND main.subject_code = sub.subject_code;