mysql - 如何在嵌套子查询中引用主查询的表？

Question

我有一个名为passive 的表，其中包含每个用户的时间戳事件列表。我想填充属性duration，它对应于当前行的事件和该用户完成的下一个事件之间的时间。

我尝试了以下查询:

UPDATE passive as passive1
SET passive1.duration = (
    SELECT min(UNIX_TIMESTAMP(passive2.event_time) - UNIX_TIMESTAMP(passive1.event_time) )
    FROM passive as passive2
    WHERE passive1.user_id = passive2.user_id 
    AND UNIX_TIMESTAMP(passive2.event_time) - UNIX_TIMESTAMP(passive1.event_time) > 0
);

这将返回错误消息错误 1093 - 您无法在 FROM 中指定要更新的目标表。

为了规避这个限制，我尝试遵循https://stackoverflow.com/a/45498/395857中给出的结构。 ，它在 FROM 子句中使用嵌套子查询来创建隐式临时表，这样它就不会被视为我们正在更新的同一个表:

UPDATE passive 
SET passive.duration = (

    SELECT *
    FROM (SELECT min(UNIX_TIMESTAMP(passive2.event_time) - UNIX_TIMESTAMP(passive.event_time)) 
        FROM passive, passive as passive2
        WHERE passive.user_id = passive2.user_id 
        AND UNIX_TIMESTAMP(passive2.event_time) - UNIX_TIMESTAMP(passive1.event_time) > 0
        )
    AS X
);

但是，嵌套子查询中的passive 表并不引用与主查询中相同的passive。因此，所有行都具有相同的 passive.duration 值。如何在嵌套子查询中引用主查询的passive？ (或者也许有一些替代方法来构造这样的查询？)

Answer 1

尝试这样......

UPDATE passive as passive1
SET passive1.duration = (
    SELECT min(UNIX_TIMESTAMP(passive2.event_time) - UNIX_TIMESTAMP(passive1.event_time) )
    FROM (SELECT * from passive) Passive2
    WHERE passive1.user_id = passive2.user_id 
    AND UNIX_TIMESTAMP(passive2.event_time) - UNIX_TIMESTAMP(passive1.event_time) > 0
    )
;