--各科目の平均点を算出
WITH AVGsub AS (
    SELECT
        subject_code
        ,AVG(score) AS subscore
    FROM
        scores
    GROUP BY
        subject_code
        )
--TRUEから更新
UPDATE
    scores
SET
    passed = TRUE
FROM
    AVGsub
WHERE
    scores.subject_code = AVGsub.subject_code
    AND score >= AVGsub.subscore;
--FALSEの更新
UPDATE
    scores
SET
    passed = FALSE
WHERE
    passed IS NULL;
SELECT * FROM scores;