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;