使用子查询更新SQL，该子查询引用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将列的值更新到表中的一堆行中。问题是，我需要使用子查询来派生这个列的值，它依赖于同一个表。这是查询:

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:

通常，如果老师和学生在两个不同的表，mysql不会抱怨。但是由于它们都使用同一个表，mysql会输出这个错误:

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

错误1093 (HY000):您不能在FROM子句中指定目标表“student”。

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?

如果没有，是否还有另一种方法可以编写这个update 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';

7 个解决方案

#1

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

#2

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.

希望这帮助别人。

#3

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

可以使用update SQL语句编写子查询，不需要为该表提供别名。给子查询表赋予别名。我试着和它对我的工作很好....

#4

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

#5

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

#6

I get "Error: Invalid use of group function" when I do that.

当我这样做时，会得到“Error: group function无效使用”。

But this post: mysql query to update field to max(field) + 1

但是这篇文章:mysql查询将字段更新为max(field) + 1

shows an even-more-nested-subselect thing, that works.

显示一个更加确定的子选择，它可以工作。

#7

-3

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

#1