with ss as (
select student_code, subject_code, rank()OVER(partition by subject_code order by score desc) as ranking
from scores
)
update scores 
set  ranking = ss.ranking
from ss
where scores.student_code = ss.student_code and scores.subject_code = ss.subject_code
;