UPDATE scores
SET passed = CASE
WHEN score >= A.avg THEN TRUE
ELSE FALSE END
FROM(
SELECT subject_code,avg(score)AS avg
FROM scores
GROUP BY subject_code
)AS A
WHERE scores.subject_code = A.subject_code;