with不能直接update，必须嵌套进update语句中；sql标准规定cte仅定义临时结果集，更新需通过update...from（postgresql）、update...from子查询（sql server）或update...join派生表（mysql 8.0+）实现。
WITH 不能直接 UPDATE，必须嵌套进 UPDATE 语句中
SQL 标准里 WITH 本身不支持独立执行写操作；它只是定义临时结果集（CTE），要更新数据，必须把 CTE 当作子查询或 JOIN 源，再套进 UPDATE。PostgreSQL 和 SQL Server 支持 UPDATE ... FROM ... 或 UPDATE ... USING ... 语法来关联 CTE；MySQL 8.0+ 虽支持 CTE，但 UPDATE 不能直接 JOIN CTE，得用派生表包装一层。
常见错误现象：ERROR: syntax error at or near "WITH"（在 MySQL 中直接写 WITH cte AS (...) UPDATE ...）或 ERROR: relation "cte" does not exist（在 PostgreSQL 中漏掉 USING 或写错作用域）。
- PostgreSQL 正确写法：用
UPDATE ... FROM ...或更推荐的UPDATE ... USING (WITH ...) AS cte ... - SQL Server：支持
UPDATE t SET ... FROM (WITH ...) AS cte JOIN t ON ... - MySQL：必须把 CTE 包进子查询，例如
UPDATE tbl JOIN (WITH cte AS (...) SELECT * FROM cte) AS tmp ON ... SET ...
多层级计算必须用递归 CTE + 显式 JOIN 更新目标表
如果“多层级”指父子关系（如组织架构、评论回复链），靠普通 CTE 无法自关联展开层级；必须用递归 CTE（WITH RECURSIVE）先算出每行应更新的值（比如祖先路径、层级深度、聚合权重），再和原表 JOIN 更新。关键点是：递归结果里必须保留能唯一匹配原表的字段（如 id），否则更新会丢失目标。
示例场景：给树形表 comments(id, parent_id, score) 的每个节点更新 total_score 字段，等于自身及所有后代 score 之和。
WITH RECURSIVE tree AS ( SELECT id, score, id AS root_id FROM comments WHERE parent_id IS NULL UNION ALL SELECT c.id, c.score, t.root_id FROM comments c JOIN tree t ON c.parent_id = t.id ), agg AS ( SELECT root_id, SUM(score) AS sum_score FROM tree GROUP BY root_id ) UPDATE comments c SET total_score = a.sum_score FROM agg a WHERE c.id = a.root_id;
- 递归 CTE 必须有非递归分支（anchor）和递归分支（union all 后部分），且递归引用名必须与 CTE 名一致
-
root_id是关键桥梁字段——它让每条原始记录都能被聚合结果定位到 - 别在递归 CTE 里做
UPDATE或INSERT，会报错
CTE 更新性能差？多数时候是 JOIN 条件没走索引
CTE 本身不缓存也不物化（除 PostgreSQL 的 MATERIALIZED 提示外），优化器仍可能重复计算；但真正拖慢更新的，往往是 CTE 输出后与主表 JOIN 时没走索引。比如用字符串拼接字段做关联、或 JOIN 条件含函数（UPPER(name)），都会让索引失效。
- 检查执行计划：PostgreSQL 用
EXPLAIN ANALYZE，看Hash Join是否出现大量Rows Removed by Filter - 确保
JOIN字段类型一致（比如 CTE 输出INT，目标表对应列也是INT，而非TEXT） - 若 CTE 结果集小（/*+ MATERIALIZE */（Oracle）或显式建临时表替代 CTE，避免重复计算
MySQL 8.0 的 WITH + UPDATE 组合容易忽略括号包裹
MySQL 不允许 WITH 直接出现在 UPDATE 开头，但允许它作为子查询出现在 JOIN 右侧——此时必须用括号把整个 CTE 查询包住，否则解析失败。
错误写法：UPDATE t1 JOIN WITH cte AS (...) SELECT * FROM cte ON ... → 报错 ERROR 1064
正确写法：
UPDATE t1
JOIN (WITH cte AS (SELECT id, val FROM t2 WHERE cond)
SELECT id, val FROM cte) AS tmp
ON t1.id = tmp.id
SET t1.x = tmp.val;
- 括号不是可选的：MySQL 解析器要求子查询必须用
(...)包裹，CTE 在其中只是定义方式之一 - CTE 内部不能引用外部表别名（比如
WITH cte AS (SELECT t1.y FROM t2)是合法的，但WITH cte AS (SELECT t1.y FROM t2 WHERE t2.z = t1.x)会报错） - 如果 CTE 需要多次引用，MySQL 会重复执行它，没有自动物化，性能敏感场景建议改用临时表
CTE 简化逻辑的前提是清晰拆分「计算」和「更新」两个阶段；很多人卡在第一阶段就试图塞进 UPDATE，结果语法错、性能差、语义乱。真正省事的地方，其实是把嵌套子查询、重复的 JOIN 条件、层层派生的中间结果，用可读的命名收拢到 WITH 里——但最后那一句 UPDATE，该写的 JOIN 还得写，该建的索引一个不能少。
