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;

SELECT '変更後' AS '状態' , * FROM scores