UPDATE scores AS SC
SET passed = CASE WHEN SC.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 SC.subject_code = sub.subject_code;