update scores
set ranking = a.ranking
from(
SELECT RANK() OVER(PARTITION BY subject_code ORDER BY score DESC) as ranking,subject_code,student_code
FROM scores) as a
where scores.subject_code = a.subject_code
and scores.student_code = a.student_code;

select * from scores