-- 合格者のデータを更新
UPDATE scores AS s
SET passed = TRUE
FROM (
    SELECT
        subject_code,
        AVG(score) AS avg
    FROM scores
    GROUP BY subject_code
) AS sub
WHERE s.subject_code = sub.subject_code
    AND s.score >= sub.avg;
-- 不合格者のデータを更新
UPDATE scores
SET passed = FALSE
WHERE passed IS NULL;