UPDATE scores 
SET passed = (average_table.average <= scores.score)
FROM (SELECT subject_code, AVG(score) AS average FROM scores GROUP BY subject_code) AS average_table
WHERE scores.subject_code = average_table.subject_code;