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