update scores as a
   set passed = (score >= b.avg_score)
  from (select subject_code,avg(score) as avg_score
          from scores
        group by subject_code) as b
 where a.subject_code = b.subject_code