子查询不能直接用于update的set子句，mysql需用派生表join绕过限制，postgresql支持from子句关联子查询，二者均需避免读写冲突、确保一对一匹配，并注意性能优化。
子查询在 UPDATE 语句里不能直接写在 SET 后面
MySQL 和 PostgreSQL 都不支持 UPDATE t1 SET col = (SELECT col FROM t2 WHERE t2.id = t1.id) 这种裸子查询写法（除非用 JOIN 或特殊语法）。直接这么写会报错，比如 MySQL 报 You can't specify target table 't1' for update in FROM clause，PostgreSQL 则可能提示 subquery must return only one column 或相关错误。
根本原因是：数据库引擎在执行 UPDATE 时，对同一张表的读写冲突做了保护，防止逻辑混乱或死锁。
- MySQL 中，必须用派生表（即给子查询加别名）绕过限制
- PostgreSQL 支持更直接的写法，但要求子查询返回单行单列且有明确关联条件
- SQL Server 允许
UPDATE ... FROM语法，本质是隐式 JOIN
MySQL 批量更新：用 JOIN + 派生表绕过限制
最稳妥的方式是把子查询包装成一个临时结果集（带别名），再和主表 JOIN。这样既避开“不能读自己”限制，又保证批量更新的原子性。
UPDATE orders o JOIN ( SELECT order_id, status AS new_status FROM order_logs WHERE created_at > '2024-01-01' ) l ON o.order_id = l.order_id SET o.status = l.new_status;
注意点：
- 子查询必须有别名（这里是
l），否则 MySQL 会报语法错误 -
JOIN条件要确保一对一，否则可能意外更新多行或漏更新 - 如果子查询没匹配到记录，对应行不会被更新（安全，但需确认是否符合业务预期）
- 避免在子查询里引用正在更新的表名（如
orders），否则仍会触发限制
PostgreSQL 批量更新：用 FROM 子句关联子查询
PostgreSQL 允许在 UPDATE 后直接跟 FROM，这是它比 MySQL 更自然的支持方式。
UPDATE orders SET status = l.new_status FROM ( SELECT order_id, status AS new_status FROM order_logs WHERE created_at > '2024-01-01' ) l WHERE orders.order_id = l.order_id;
关键细节：
-
FROM后的子查询可以是任意复杂度，只要最终能通过WHERE关联上主表 - 子查询中不能出现
orders表名（哪怕加了 schema 前缀），否则会报relation "orders" does not exist - 如果子查询对同一
order_id返回多行，UPDATE 会非确定性地选其中一行 —— 必须加DISTINCT ON或聚合来规避
UPDATE 子查询性能差？先确认是否真需要子查询
很多场景下，所谓“子查询更新”，其实是误用了逻辑。比如想把用户最新订单状态同步到用户表，其实更适合用窗口函数预计算，或拆成两步（先写入临时表，再 JOIN 更新）。
- 子查询每次执行都可能全表扫描，尤其当
order_logs很大而只更新少量orders时，效率极低 - 考虑加覆盖索引：
CREATE INDEX idx_logs_order_time ON order_logs(order_id, created_at, status) - 如果只是按固定规则更新（如所有未支付订单设为已取消），直接写
WHERE status = 'pending'比子查询快得多 - 跨库或跨实例更新？子查询基本不可行，得用应用层或 ETL 工具
真正难处理的是那些依赖另一张表最新状态、且无法用简单 JOIN 表达的逻辑 —— 这时候才值得花精力优化子查询结构，而不是默认把它当首选方案。
