带有子查询的SQL UPDATE，该子查询引用了MySQL中的同一张表 [英] SQL UPDATE with sub-query that references the same table in MySQL

问题描述

我正在尝试使用UPDATE更新表中一堆行中的列值.问题是我需要使用子查询来导出该列的值，并且它依赖于同一张表.这是查询:

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

通常，如果老师和学生在两个不同的表中，则mysql不会抱怨.但是由于它们都使用同一个表，因此mysql会发出此错误:

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:

错误1093(HY000):您无法在FROM子句中指定要更新的目标表学生"

有什么办法可以强制mysql执行更新?我是100％肯定的，因为行已更新，所以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.

如果没有，还有另一种方法可以编写此更新sql以实现相同的效果吗?

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

谢谢！

我想我可以使用它:

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

推荐答案

为您提供的一些参考资料 http ://dev.mysql.com/doc/refman/5.0/en/update.html

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

这篇关于带有子查询的SQL UPDATE，该子查询引用了MySQL中的同一张表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！