为什么MySQL执行UPDATE语句时无法访问临时创建的关系的列？

• 定义的CTE临时结果集avg_salary未与更新表instructor建立关联，UPDATE语句的查询范围默认不包含未显式引入的CTE，因此WHERE子句无法识别avg_salary.value字段。报错信息里的avg.value属于笔误，要么是实际运行时错将CTE别名设为了avg，要么是代码简化时的名称写错，核心问题都是CTE未被正确纳入更新逻辑。
• 不同数据库对UPDATE+CTE的语法支持存在差异，部分数据库（如MySQL）不支持直接在UPDATE语句前定义CTE后直接引用字段，必须通过关联操作将CTE加入更新的数据源。

方案1：使用会话变量存储平均薪资（兼容性最高、性能最优） #

提前仅计算一次平均薪资存入会话变量，完全避免重复计算，同时更新过程中平均值不会随薪资变动发生变化，完全匹配你的需求：

-- 单次计算平均薪资并存入会话变量
SET @avg_salary = (SELECT AVG(salary) FROM instructor);
-- 直接调用变量完成更新
UPDATE instructor
SET salary = salary * 1.05
WHERE salary < @avg_salary;

方案2：关联子查询实现（无需变量） #

如果不想使用变量，可以将平均薪资的计算结果作为关联表和instructor表绑定，保证平均值仅计算一次：

MySQL 8.0+ 写法 #

UPDATE instructor i
JOIN (SELECT AVG(salary) AS avg_val FROM instructor) AS avg_salary
ON i.salary < avg_salary.avg_val
SET i.salary = i.salary * 1.05;

PostgreSQL 写法 #

WITH avg_salary(avg_val) AS (
    SELECT AVG(salary) FROM instructor
)
UPDATE instructor
SET salary = salary * 1.05
FROM avg_salary
WHERE instructor.salary < avg_salary.avg_val;

补充说明：你最初担心的子查询每行重复计算平均值的问题，目前主流数据库的查询优化器都会自动将无关联的标量子查询优化为仅执行一次，不会出现每行重算的性能问题，但更新过程中薪资变化会导致平均值变动的问题确实存在，因此提前将平均值固化到变量/临时结果集的方案是正确的。

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