I needed this for SQL Server. Here it is:
UPDATE user_account
SET student_education_facility_id = cnt.education_facility_id
from (
SELECT user_account_id,education_facility_id
FROM user_account
WHERE user_type = 'ROLE_TEACHER'
) as cnt
WHERE user_account.user_type = 'ROLE_STUDENT' and cnt.user_account_id = user_account.teacher_id
I think it works with other RDBMSes (please confirm). I like the syntax because it's extensible.
The format I needed was this actually:
UPDATE table1
SET f1 = cnt.computed_column
from (
SELECT id,computed_column --can be any complex subquery
FROM table1
) as cnt
WHERE cnt.id = table1.id