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