UPDATE scores
    SET passed = TRUE
    FROM (
        SELECT 
            sc.test_code as test_code,
            sc.student_code as student_code,
            sc.subject_code as subject_code,
            sc.score as score,
            av.average_score as average_score
        FROM scores AS sc
        INNER JOIN (
            SELECT 
                subject_code,
                AVG(score) as average_score
            FROM scores
            GROUP BY subject_code            
        ) as av
        ON sc.subject_code = av.subject_code
    ) AS sub
    WHERE scores.student_code = sub.student_code AND
    scores.subject_code = sub.subject_code AND
    sub.score >= sub.average_score;
UPDATE scores
    SET passed = FALSE
    WHERE passed is NULL;