mysql - 在查找子查询上引用主表进行更新时出错

Question

首先，这个问题之前没有得到解答，因为有关错误 1093 的线程显示了一个简单的子查询。就我而言，我正在查找引用主表的下一条记录。在未阅读整个问题之前，请不要将其标记为重复项。

我需要使用下一条记录的数据(根据gkey字段，它是连续的int主键)来更新日期错误(1970-01-01)的表的记录。

所以，如果我执行此查询:

SELECT aa.gkey,
       aa.course_date,
       (select course_date from BI.fact_training_event_tbl bb where bb.gkey = (
            select min(cc.gkey) 
            from BI.fact_training_event_tbl cc 
                 where cc.gkey > aa.gkey)) as next_date
from BI.fact_training_event_tbl aa
where course_date = '1970-01-01'

它按照预期正确地提供了记录:

gkey   course_date  next_date
====   ===========  =========
4103   1970-01-01   2017-03-23
4884   1970-01-01   2017-03-22
5047   1970-01-01   2017-03-23

我现在需要使用 next_date 更新 course_date 字段，但如果我尝试运行以下命令:

update BI.fact_training_event_tbl aa
    set course_date =
    (select course_date from BI.fact_training_event_tbl bb where bb.gkey = (
            select min(cc.gkey)
    from BI.fact_training_event_tbl cc
         where cc.gkey > BI.fact_training_event_tbl.gkey))
where course_date = '1970-01-01'

我收到错误:

Error Code 1093. You can't specify target table 'BI.fact_training_event_tbl' for update in FROM clause

我尝试按照此处推荐的操作:MySQL Error 1093 - Can't specify target table for update in FROM clause ，将查询嵌套在另一个查询中:

update BI.fact_training_event_tbl as zz
    set course_date =
    (select course_date from
    (select course_date from BI.fact_training_event_tbl as bb where bb.gkey = (
            select min(cc.gkey) 
      from BI.fact_training_event_tbl as cc
           where cc.gkey > gkey)) as aa )
where course_date = '1970-01-01'

但我得到的只是将 date_course 设置为 null，而不是 next_date。

如果我尝试像这样引用主表:

where cc.gkey > BI.fact_training_event_tbl.gkey

或

where cc.gkey > zz.gkey

它说:未知列 BI.fact_training_event_tbl.gkey 或 zz.gkey。

关于如何实现这一目标有什么想法吗？

Answer 1

1093 错误的根本原因是 MySQL 无法访问您想要再次更新的表(与该表有任何直接依赖关系)。

尽管您链接的解决方法看起来只是在原始子查询周围添加一个 select 层，例如select * from (你的原始子查询)，你错过了它们工作的原因:它们使用派生表而不是(依赖)子查询(这就是@Cheekysoft对隐式临时表的含义 在你的 linked answer 中)。派生表不能依赖于外部查询(因此根本问题就消失了)。它或多或少被像任何实际的表一样对待(注意，例如，您必须命名一个派生表，在您的情况下为aa；请参阅例如 another answer of mine 了解更多详细信息关于此)。

但这也意味着你不能在这里使用对外表的任何依赖，无论你想如何欺骗 MySQL 这样做。你例如得到未知列错误，因为此时外部查询无法访问以供引用。

因此基本策略是将您需要的所有行放入派生表中，然后执行 join 来选择您需要更新实际行的行:

update fact_training_event_tbl
join ( your original select that returns 3 rows ) base
on base.gkey = fact_training_event_tbl.gkey
set course_date = base.course_date

在派生表(base)的“内部”，您可以做任何您想做的事情并根据需要经常使用fact_training_event_tbl，但是依赖于外部 fact_training_event_tbl 是通过 on 条件在 base 的“外部”完成的。

由于不仅base是一个(派生)表，而且fact_training_event_tbl也是一个(实际)表，所以通常也可以这样做

update fact_training_event_tbl
join fact_training_event_tbl base
on base.gkey = fact_training_event_tbl.gkey + 1
set course_date = base.course_date

就您而言，如果您的意思是“gkey 字段，它是连续的 int 主键”(因此没有间隙)，那么这将起作用。但即使不是，它也应该说明在这种情况下使用普通表和派生表之间的类比。