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