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

UPDATE scores
SET passed = FALSE
WHERE passed IS NULL;

SELECT * FROM scores;