代码之家 › 专栏 › 技术社区 › egervari

使用引用MySQL中相同表的子查询进行SQL更新

mysql-error-1093 sql mysql

egervari · 技术社区 · 15 年前

我正在尝试使用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反而会抛出这个错误:

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

6 回复 | 直到 15 年前

John Hartsock 15 年前

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

Simke Nys Simon Arnold 9 年前

具有更清晰的表和列名的抽象示例:

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

由@Nico建议

希望这能帮助别人。

JJD 10 年前

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'

上面是更新查询的示例。。。

可以用update SQL语句编写子查询,不需要为该表指定别名。为子查询表提供别名。我试过了,效果很好。。。。

MikO 13 年前

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

Mahmood Dehghan 11 年前

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

我认为它可以和其他RDBMSE一起工作(请确认)。我喜欢语法,因为它是可扩展的。

我需要的格式实际上是:

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

-3

Beau Grantham 14 年前

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