子查询在update中必须用join重写，因sql标准禁止直接引用被更新表以避免语义歧义；mysql需派生表+join，postgresql支持update...from，sql server用update...from关联子查询；所有操作基于事务快照，高并发下可能产生数据偏差。
子查询在UPDATE语句中必须用JOIN重写
SQL标准（包括MySQL 5.7+、PostgreSQL、SQL Server）明确禁止在UPDATE的SET或WHERE子句中直接引用被更新的表，否则会报错ERROR 1093 (HY000): You can't specify target table 't' for update in FROM clause（MySQL）或类似提示。这不是语法疏漏，而是为避免语义歧义和执行时序问题——数据库无法安全确定子查询是基于更新前还是更新后快照计算的。
真实场景中，“基于历史状态更新”通常指：用同一张表中其他行（比如上一条记录、最新有效状态、某时间点快照）的字段值，去修正当前行。这类需求不能靠裸子查询实现，必须转为显式JOIN。
- MySQL用户必须用
UPDATE ... JOIN语法，把子查询先包装成派生表（derived table），再关联原表 - PostgreSQL支持
UPDATE ... FROM，可直接FROM子查询，但子查询里仍不能出现目标表名 - SQL Server用
UPDATE t SET ... FROM t INNER JOIN (subquery) s ON ...结构
MySQL中用派生表实现“取上一条记录的状态”更新
例如有一张orders表，想为每条记录的prev_status字段填入按created_at排序的前一行的status值。不能写UPDATE orders SET prev_status = (SELECT status FROM orders o2 WHERE o2.created_at ——这会直接报错1093。
正确做法是把子查询变成匿名派生表，并用JOIN关联：
UPDATE orders AS t1
JOIN (
SELECT
o1.id,
(SELECT o2.status
FROM orders o2
WHERE o2.created_at
<p>注意两点：派生表<code>t2</code>里用<code>o1</code>别名访问原表，外部<code>t1</code>才是被更新主体；子查询里的<code>o2</code>和外部<code>t1</code>完全隔离，不会触发1093错误。</p>
<h3>PostgreSQL中用UPDATE...FROM避免嵌套子查询性能陷阱</h3>
<p>PostgreSQL允许<code>UPDATE ... FROM</code>，看起来更简洁，但容易误写成低效形式。比如想用最近一次成功支付记录的金额更新用户余额：</p>
<p>❌ 错误示范（N+1式扫描）：<br><code>UPDATE users SET balance = (SELECT amount FROM payments WHERE user_id = users.id AND status = 'success' ORDER BY paid_at DESC LIMIT 1)</code></p><div class="aritcle_card flexRow artxards">
<div class="artcardd flexRow">
<a class="aritcle_card_img" rel="nofollow" href="/xiazai/skill2334" title="MySQL"><img
src="https://img.php.cn/upload/skill/000/000/081/178900927846657.jpg" alt="MySQL" onerror="this.onerror='';this.src='/static/lhimages/moren/morentu.png'" ></a>
<div class="aritcle_card_info flexColumn">
<a rel="nofollow" href="/xiazai/skill2334" title="MySQL" class="overflowclass">MySQL</a>
<p class="overflowclass">编写正确的MySQL查询，避免字符集、索引和锁方面的常见陷阱。</p>
</div>
<a rel="nofollow" href="/xiazai/skill2334" title="MySQL" class="aritcle_card_btn flexRow flexcenter"><b></b><span>下载</span>
</a>
</div>
</div>
<p>✅ 正确做法（单次聚合扫描）：<br></p><pre class="brush:php;toolbar:false;">UPDATE users u
SET balance = p.last_amount
FROM (
SELECT user_id, MAX(amount) AS last_amount
FROM payments
WHERE status = 'success'
GROUP BY user_id
) p
WHERE u.id = p.user_id;
这里的关键是把聚合逻辑放在FROM子查询里，而不是塞进SET右边的标量子查询——后者会导致对每个users行都执行一次全表扫描payments，而前者只扫一次payments并做GROUP BY，性能差异可达数量级。
WHERE条件里依赖历史状态时，优先用窗口函数预计算
当更新逻辑需要判断“该记录是否是某用户的第一笔订单”或“是否比上一笔晚于7天”，硬套子查询JOIN会很笨重。此时应先用CTE或子查询加窗口函数算出标记列，再UPDATE：
以MySQL 8.0+为例：
WITH ranked AS (
SELECT id, user_id, created_at,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at) AS rn,
LAG(created_at) OVER (PARTITION BY user_id ORDER BY created_at) AS prev_created
FROM orders
)
UPDATE orders o
JOIN ranked r ON o.id = r.id
SET o.is_first = IF(r.rn = 1, 1, 0),
o.is_late = IF(DATEDIFF(o.created_at, r.prev_created) > 7, 1, 0);
这个写法比在UPDATE里反复调用(SELECT ... FROM orders ...)清晰得多，也更容易调试。如果数据库不支持CTE（如MySQL 5.7），就用派生表替代，但逻辑结构保持一致。
最易被忽略的是：所有这类操作都默认基于事务快照执行，如果你在长事务里跑这类UPDATE，子查询看到的历史数据可能不是你期望的“实时最新”，而是事务启动时刻的快照——这点在高并发更新场景下会引发隐性数据偏差。
