UPDATE scores
SET passed = case when 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 scores.subject_code = sub.subject_code;

select * from scores