Edit Question - KnowledgeHub

Title Body

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

Tags (comma-separated) Cancel