如何改写MySQL UPDATE INNER JOIN查询使t1列可在子查询中访问

问题原因 #

原查询中，FROM子句内的派生表（t2、t3）属于非相关子查询，会独立于外部UPDATE语句执行，因此无法直接引用外部表t1的列，导致Unknown column 't1.col2' in 'where clause'错误。

改写后的查询语句 #

方案一：使用EXISTS相关子查询（推荐，逻辑清晰且高效） #

UPDATE MyFirstTable AS t1
INNER JOIN MySecondTable AS t2 ON t1.col1 = t2.col1
SET t2.col3 = t1.col3
WHERE t2.col2 > t1.col2
  AND EXISTS (
    SELECT 1
    FROM MySecondTable AS t3
    WHERE t3.col1 = t1.col1
      AND t3.col2 < t1.col2
  );

方案二：使用多表JOIN筛选符合条件的行 #

UPDATE MyFirstTable AS t1
INNER JOIN (
    SELECT DISTINCT t2.col1, t2.col2
    FROM MySecondTable AS t2
    JOIN MySecondTable AS t3 ON t2.col1 = t3.col1
    WHERE t2.col2 > t1.col2
      AND t3.col2 < t1.col2
) AS filtered_t2 ON t1.col1 = filtered_t2.col1
INNER JOIN MySecondTable AS target 
  ON target.col1 = filtered_t2.col1 
  AND target.col2 = filtered_t2.col2
SET target.col3 = t1.col3;

改写说明 #

1. 方案一中，EXISTS子查询是相关子查询，会与外部的t1表逐行关联，能够正常访问t1.col2，同时验证同一col1下是否存在col2小于t1.col2的行。
2. 方案二中，先通过自连接MySecondTable筛选出符合条件的行，再与t1关联执行更新，同样避免了非相关子查询无法访问外部列的问题。

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