UPDATE scores 
SET ranking = 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