WITH T AS(
    SELECT
        student_code,
        subject_code,
        AVG(score) OVER(PARTITION BY subject_code) AS avg_score
    FROM
        scores
)
UPDATE
    scores
SET
    passed =
    CASE
        WHEN score >= avg_score THEN TRUE
        ELSE FALSE
    END
FROM
    T
WHERE
    scores.student_code = T.student_code
    AND scores.subject_code = T.subject_code