MySQL中引用同一表格的子查询的SQL UPDATE语句

Question

我尝试使用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';

通常情况下，如果老师和学生在两个不同的表中，mysql 就不会抱怨。但是由于它们都使用同一张表，因此 mysql 会报出以下错误：

ERROR 1093 (HY000)：您无法在 FROM 子句中指定目标表 'student' 进行更新

有没有办法强迫 mysql 进行更新？我可以百分之百确定 from 子句不会受到影响，因为行将被更新。

如果不能，是否有其他方法可以编写这个更新 SQL 以实现相同的效果？

谢谢！

编辑：我想我已经让它工作了：

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

- egervari

Answer 1

这里有一些参考资料供您使用 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

- John Hartsock

---

如果有两种解决方案就好了：一种是具体的，另一种则稍微抽象一些，表格和列名更加清晰易懂，以简化寻求一般答案的人们的阅读，比如我。无论如何，问题和答案都让我满意，但我提出这个建议是为了更好地理解社区。谢谢，对问题和答案都点个赞。 - Nico

完全同意@Nico的观点。这就是为什么我提供了一个带有更清晰的表格和列名的抽象示例（https://dev59.com/iW855IYBdhLWcg3wj1MS#23772515）。我还赞同了John Hartsock的答案（对我创建示例非常有帮助）。 - Simon Arnold

Answer 2

更清晰的表格和列名示例摘要:

UPDATE tableName t1
INNER JOIN tableName t2 ON t2.ref_column = t1.ref_column
SET t1.column_to_update = t2.column_desired_value

正如 @Nico 所建议的

希望这能帮助到某些人。

- Simon Arnold

---

例如，ON t2.ref_column = t1.column_to_update子句不应该引用column_to_update。连接条件可能基于不同的列。 - frankleonrose

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

以下是示例更新查询语句...

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

- Ricky Patel

---

当子查询中未指定别名时，它不会从内部表中获取列吗？ - Nish

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

- Sin2

Answer 5

我需要这个用于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

我认为它可以与其他关系型数据库管理系统一起使用（请确认）。我喜欢这种语法，因为它是可扩展的。

实际上，我需要的格式是这样的：

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

- Mahmood Dehghan

---

太棒了，我用了类似的东西，非常顺利，谢谢！（Microsoft SQL SERVER） - Pedro Emilio Borrego Rached

Answer 6

我需要在子查询中进行求和，但是没有任何解决方案可行。

所以我在主表上创建了一个视图，并可以从中访问相同的表。

CREATE USER_ACCOUNT_VW AS SELECT * FROM USER_ACCOUNT

UPDATE user_account
SET user_account.student_education_facility_id =
    (SELECT user_account_VW.education_facility_id
     FROM user_account_VW
     WHERE user_account_VW.user_account_id = user_account.teacher_id
         AND user_account_VW.user_type = 'ROLE_TEACHER')

- Olivier Quatre Bornes

---

嗨，Olivier，你能否给你的问题添加标签？欢迎来到Stackoverflow :) - Ashraf Alshahawy

Answer 7

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

- Magizh

---

简单的解释总是更好，可以使你的回答更有价值。 - ForceMagic