update scores as sc
set passed = case when sc.score >= sub.ave then true
    else false
    end
from (select subject_code, avg(score) as ave
    from scores
    group by subject_code) as sub
where sc.subject_code = sub.subject_code ;