update scores set ranking  = sub.rank 
from 
     ( select subject_code, score, rank() over ( partition by subject_code order by score ) as rank from scores ) as sub
where scores.subject_code = sub.subject_code
    and scores.score = sub.score