update scores 
 set passed = TRUE
 from (select avg((select score from scores where subject_code = 'S00001')) as avg1 from scores) as sub
where  score >= sub.avg1 and subject_code = 'S00001';

update scores 
 set passed = TRUE
 from (select avg((select score from scores where subject_code = 'S00002')) as avg2 from scores) as sub
where  score >= sub.avg2 and subject_code = 'S00002';

update scores 
 set passed = TRUE
 from (select avg((select score from scores where subject_code = 'S00003')) as avg3 from scores) as sub
where  score >= sub.avg3 and subject_code = 'S00003';

update scores
 set passed = FALSE
where passed is null;