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
WHERE scores.subject_code = SUB.subject_code;
SELECT * FROM scores