update scores 
set
    passed = TRUE 
from
    (select subject_code, AVG(score) as avg
        from scores
        group by subject_code
    ) as sub
where
    score >= sub.avg and scores.subject_code = sub.subject_code;

update scores 
set
    passed = FALSE 
from
    ( 
        select
            subject_code
            , AVG(score) as avg 
        from
            scores 
        group by
            subject_code
    ) as sub 
where
    score < sub.avg and scores.subject_code = sub.subject_code;