update scores as s
set passed =
case when s.score >= sub.avg_score 
then TRUE
else FALSE
end
from (
    select subject_code, avg(score) as avg_score from scores group by subject_code
) as sub
where s.subject_code = sub.subject_code;

select * from scores;