title: SQL UPDATE with sub-query that references the same table in MySQL
body: <p>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:</p>
  
  <pre><code>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';
  </code></pre>
  
  <p>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:</p>
  
  <p><strong>ERROR 1093 (HY000): You can't specify target table 'student' for update in FROM clause</strong></p>
  
  <p>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. </p>
  
  <p>If not, is there another way I can write this update sql to achieve the same affect?</p>
  
  <p>Thanks!</p>
  
  <p>EDIT: I think I got it to work:</p>
  
  <pre><code>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';
  </code></pre>