UPDATE	scores
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				
		INNER JOIN scores AS sc			
			ON sc.student_code = sub.student_code
WHERE	sc.subject_code = sub.subject_code;