带有引用 MySQL 中相同表的子查询的 SQL UPDATE

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

提问by egervari

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 更新表中一堆行中的列值。问题是我需要使用子查询来导出此列的值，并且它依赖于同一个表。这是查询：

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:

通常，如果老师和学生在 2 个不同的表中，mysql 不会抱怨。但是由于它们都使用同一个表，mysql 反而抛出了这个错误：

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

错误 1093 (HY000)：您不能在 FROM 子句中指定要更新的目标表“学生”

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.

有什么办法可以强制mysql进行更新吗？我 100% 肯定 from 子句不会在行更新时受到影响。

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

如果没有，是否有另一种方法可以编写此更新 sql 来实现相同的效果？

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

回答by John Hartsock

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

一些参考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

回答by Simon Arnold

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

正如@Nico 所建议的

Hope this help someone.

希望这有助于某人。

回答by Ricky Patel

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....

您可以使用更新 SQL 语句编写子查询，您不需要为该表提供别名。为子查询表提供别名。我试过了，对我来说效果很好......

回答by Mahmoodvcs

I needed this for SQL Server. Here it is:

我需要这个用于 SQL Server。这里是：

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.

我认为它适用于其他 RDBMS（请确认）。我喜欢这种语法，因为它是可扩展的。

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

回答by Sin2

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

回答by Magizh

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