UPDATE scores
SET passed =
    CASE
    WHEN score >= sub.avg THEN TRUE
    ELSE FALSE END
FROM (
    SELECT subject_code,AVG(score) AS avg
    FROM scores
    GROUP BY subject_code
) AS sub
where scores.subject_code = sub.subject_code;
--select * from scores