UPDATE scores AS s
SET passed  = TRUE
FROM (SELECT subject_code, AVG(score) AS avg FROM scores GROUP BY subject_code) AS sub
WHERE s.subject_code = sub.subject_code AND s.score >= sub.avg;

UPDATE scores AS s
SET passed  = FALSE
FROM (SELECT subject_code, AVG(score) AS avg FROM scores GROUP BY subject_code) AS sub
WHERE s.subject_code = sub.subject_code AND s.score < sub.avg;

SELECT * FROM scores;