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