update scores
set passed = case when C.score >= C.score_avg then TRUE ELSE FALSE END
from
(
select *
from
    scores as A
inner join
(select subject_code, avg(score) as score_avg
from scores
group by subject_code
) as B
on A.subject_code = B.subject_code
) as C
where scores.student_code = C.student_code and scores.subject_code = C.subject_code;
    
select * from scores