SQL UPDATE with sub-query that references the same table in MySQL

Question

I'm trying to update a column's value in a bunch of rows in a table using UPDATE. The problem is that I need to use a sub-query to derive the value for this column, and it depends on the same table. Here's the query:

UPDATE user_account student SET student.student_education_facility_id = (    SELECT teacher.education_facility_id    FROM user_account teacher    WHERE teacher.user_account_id = student.teacher_id AND teacher.user_type = 'ROLE_TEACHER' ) WHERE student.user_type = 'ROLE_STUDENT'; 

Ordinarily if teacher and student were in 2 different tables, mysql wouldn't complain. But since they are both using the same table, mysql spews out this error instead:

ERROR 1093 (HY000): You can't specify target table 'student' for update in FROM clause

Is there any way I can force mysql to do the update? I am 100% positive the from clause will not be affected as the rows are updated.

If not, is there another way I can write this update sql to achieve the same affect?

Thanks!

EDIT: I think I got it to work:

UPDATE user_account student LEFT JOIN user_account teacher ON teacher.user_account_id = student.teacher_id SET student.student_education_facility_id = teacher.education_facility_id WHERE student.user_type = 'ROLE_STUDENT'; 

Answer 1

Some reference for you http://dev.mysql.com/doc/refman/5.0/en/update.html

UPDATE user_account student  INNER JOIN user_account teacher ON    teacher.user_account_id = student.teacher_id     AND teacher.user_type = 'ROLE_TEACHER' SET student.student_education_facility_id = teacher.education_facility_id 

Answer 2

Abstract example with clearer table and column names:

UPDATE tableName t1 INNER JOIN tableName t2 ON t2.ref_column = t1.ref_column SET t1.column_to_update = t2.column_desired_value 

As suggested by @Nico

Hope this help someone.