UPDATE scores
SET passed = CASE WHEN SUB.AVERAGE <= score THEN TRUE
ELSE FALSE END
FROM (SELECT subject_code,
AVG(score) AS AVERAGE
FROM scores
GROUP BY subject_code) AS SUB