首页 文章

SQL UPDATE与子查询引用MySQL中的同一个表

提问于
浏览 次
40

我正在尝试使用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): You can't specify target table 'student' for update in FROM clause

有什么办法可以强制mysql进行更新吗？我100％肯定，因为更新行，所以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';
mysql sql mysql-error-1093

6 回答

  • 2

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

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

    我需要的格式实际上是：

    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
    回复于
  • 5
    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';
    回复于
  • -3

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

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

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

    正如@Nico所建议的那样

    希望这能有所帮助 .

    回复于
  • 20
    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语句编写子查询，您不需要为该表提供别名 . 为别查询表提供别名 . 我试过，它对我来说很好......

    回复于
  • 44
    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';
    回复于

相关问题