UPDATE SCORES AS T1
SET PASSED = (SCORE >= T2.AVG)
FROM
(SELECT AVG(S1.SCORE) AS AVG
    ,S1.SUBJECT_CODE AS SUB
FROM SCORES AS S1
WHERE S1.TEST_CODE = 'T00001'
GROUP BY S1.SUBJECT_CODE) AS T2
WHERE T1.SUBJECT_CODE = T2.SUB;

SELECT SCORE
    ,PASSED
    ,SUBJECT_CODE
FROM SCORES
WHERE TEST_CODE = 'T00001'