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