mysql中update语句的子查询更新必须用join替代，因5.7及更早版本禁止对同一表既查又改，8.0+虽部分支持但仍受限；优先采用join写法，语义清晰、兼容性好、性能更优，且能避免in子查询导致的循环执行与性能断崖。
子查询更新必须用 JOIN 语法替代 UPDATE ... SET ... WHERE ... IN (SELECT ...)
MySQL 8.0+ 和 PostgreSQL 支持直接在 UPDATE 中嵌套子查询，但 MySQL 5.7 及更早版本会报错 You can't specify target table 'xxx' for update in FROM clause。这不是语法写错了，是引擎限制——它禁止对同一张表既查又改。绕过方式不是加别名糊弄，而是把子查询转成派生表（derived table）或显式 JOIN。
实际操作中，优先用 JOIN 写法，语义清晰、兼容性好、执行计划也更容易优化：
UPDATE orders o JOIN ( SELECT order_id, SUM(amount) AS total FROM order_items GROUP BY order_id HAVING SUM(amount) > 1000 ) t ON o.order_id = t.order_id SET o.status = 'VIP_PROCESSING';
-
JOIN子句里必须给子查询起别名（如上面的t），否则语法报错 - 不能在子查询中引用外层
UPDATE表的字段做关联（比如写WHERE order_items.order_id = o.order_id在子查询里），必须靠ON条件连接 - PostgreSQL 还支持
FROM子句写法（UPDATE t1 SET x = t2.y FROM t2 WHERE t1.id = t2.t1_id），但 MySQL 不认，跨数据库项目慎用
UPDATE 子查询里用聚合函数要小心 NULL 和多行匹配
子查询返回多行结果时，如果没加 GROUP BY 或过滤条件，JOIN 会导致主表记录被重复更新（一行变多行），甚至触发错误（如唯一键冲突）。更隐蔽的问题是：子查询某组没数据，对应主表记录就完全不会被更新——这常被误认为“SQL 没生效”。
例如想把用户最近一笔订单金额填进用户表的 last_order_amount 字段：
UPDATE users u JOIN ( SELECT user_id, MAX(created_at) AS max_time FROM orders GROUP BY user_id ) latest ON u.user_id = latest.user_id JOIN orders o ON o.user_id = latest.user_id AND o.created_at = latest.max_time SET u.last_order_amount = o.amount;
- 这里用了两层
JOIN：先找每个用户的最新下单时间，再关联出那笔订单的具体金额 - 如果某个用户没有任何订单，
latest子查询不包含他，u就不会出现在结果集中，last_order_amount保持原值（包括NULL） - 若想把无订单用户的字段设为
0，得改用LEFT JOIN+COALESCE，但注意UPDATE ... LEFT JOIN在 MySQL 中要求被左连的表必须有索引，否则可能全表扫描
WHERE 条件里用 IN (SELECT ...) 更新大表极慢
当子查询返回几千行以上，且主表有百万级数据时，WHERE id IN (SELECT id FROM ...) 的性能会断崖式下跌——MySQL 通常把它转成循环嵌套（N × M），而不是哈希连接。即使加了索引，优化器也可能选错执行计划。
实测对比（MySQL 8.0，100 万用户表，子查询返回 5 万 ID）：
- 用
IN写法：平均耗时 42 秒 - 改写为
JOIN：平均耗时 0.8 秒（依赖user_id上有索引） - 若子查询本身很重（比如含多表关联和
GROUP BY），可先用临时表缓存结果：CREATE TEMPORARY TABLE tmp_ids AS SELECT user_id FROM ...，再JOIN tmp_ids，避免重复计算
事务与锁：批量更新可能锁住远超预期的行
子查询更新不是“先算完再改”，而是在执行过程中边查边锁。比如 UPDATE t1 JOIN t2 ON ... SET t1.x = t2.y，MySQL 会对所有参与 JOIN 的 t1 和 t2 行加写锁，直到事务结束。如果子查询范围过大，可能长时间阻塞其他读写。
- 线上执行前务必用
EXPLAIN UPDATE ...（MySQL 8.0+ 支持）看实际扫描行数 - 避免在高峰期对核心业务表（如
users、orders）做全表关联更新；可分批加LIMIT（但要注意LIMIT在UPDATE ... JOIN中不支持，需改用主键范围切分） - PostgreSQL 的
UPDATE ... FROM默认走 Hash Join，锁行为更可控；但若子查询结果集太大导致内存不足，会退化为磁盘排序，同样卡顿
最麻烦的从来不是语法怎么写，而是子查询到底返回多少行、这些行在物理存储上是否散列、以及你的隔离级别有没有让别的事务正在等同一行锁——这些都得看执行计划，不能只信 SQL 表面逻辑。
