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

User · Accepted Answer

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

User · Answer

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

User · Answer

UPDATE user account  SET  student education facility id           SELECT teacher education facility id     FROM user account teacher     WHERE teacher user account id   teacher id     AND teacher user type    ROLE TEACHER    WHERE user type    ROLE STUDENT    Above are the sample update query      You can write sub query with update SQL statement  you don t need to give alias name for that table  give alias name to sub query table  I tried and it s working fine for me

User · Answer

UPDATE user account student       SELECT teacher education facility id as teacherid    FROM user account teacher    WHERE teacher user account id   student teacher id AND teacher user type    ROLE TEACHER    teach SET student student education facility id  teach teacherid WHERE student user type    ROLE STUDENT

User · Answer

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

User · Answer

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

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

Examples related to mysql

Examples related to sql

Examples related to mysql-error-1093