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; 

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;