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