update scores as s
set passed = TRUE
from (
  select
    subject_code
    , avg(score) as avg
  from
    scores
  group by
    subject_code
) as sub
where
  s.subject_code = sub.subject_code
  and
  s.score >= sub.avg
;

update scores as s
set passed = FALSE
where
  s.passed is null
;