mysql 修改语句 子查询语句,带有子查询的SQL UPDATE，该子查询引用了MySQL中的同一张表...

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';

解决方案

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