将计算1day_rank移动平均的SELECT语句重构为UPDATE语句遇MySQL报错求助

嘿，我刚好碰到过一模一样的问题，这就给你捋清楚怎么解决！

首先说你遇到的第一个错误Error Code: 1093：MySQL的查询优化器不允许你在UPDATE/DELETE的FROM子句里直接引用要更新的目标表，说白了就是怕你一边读同一张表一边写，导致数据一致性出问题，所以直接给你拦下来了。

然后是第二个1054错误，大概率是你重构内连接的时候，要么子查询里没输出用来关联的字段（比如timestamp），要么在WHERE里错误引用了外层表的字段，导致MySQL找不到这个列。

正确的重构方案：用派生表（临时表）中转 #

核心思路是把你原来计算1day_rank移动平均值的SELECT语句，包装成一个派生表（也就是临时生成的中间表），先让MySQL把计算结果存到这个临时表里，再用这个临时表和目标表关联更新，完美避开直接引用的问题。

举个具体的例子，假设你原来的SELECT语句是这样的（用来计算7天移动平均）：

SELECT 
    timestamp,
    AVG(1day_rank) OVER (ORDER BY timestamp ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS 7day_rank
FROM T1;

那对应的UPDATE语句应该改成这样：

UPDATE T1
JOIN (
    -- 把你原来的移动平均计算逻辑完整放这里，必须包含用来关联的唯一标识字段（比如timestamp或者主键id）
    SELECT 
        timestamp,
        AVG(1day_rank) OVER (ORDER BY timestamp ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS calculated_7day_rank
    FROM T1
) AS temp_table 
ON T1.timestamp = temp_table.timestamp -- 用唯一字段关联目标表和临时表
SET T1.7day_rank = temp_table.calculated_7day_rank;

关键注意点 #

• 关联字段必须唯一：如果你的timestamp字段不是唯一的（比如同一时间有多条记录），一定要换成表的主键（比如id）来关联，不然会出现重复更新的问题。
• 索引优化：如果是大表，记得给timestamp（或者你用的关联字段）加上索引，不然JOIN的时候会很慢。
• 避免锁表：如果数据量特别大，可以考虑分批次更新，比如按timestamp范围拆分，减少锁表时间。

这样改完之后，两个错误应该都能解决啦！

内容的提问来源于stack exchange，提问作者tak3shi