SQLite Studio中UPDATE子查询结果异常，单独执行子查询正常

我使用Superstore_test数据集，需要新增一列next_sales来显示下一行的销售额。执行以下UPDATE语句后，部分行的next_sales能正确对应下一行销售额，部分行却不匹配；但单独执行语句中的子查询时，next_sales的结果完全正确。

执行的SQL语句：

UPDATE Superstore_test
SET next_sales = subquery.next_sales
FROM (
  SELECT order_id, sales, LEAD(sales) OVER (ORDER BY order_id asc) AS next_sales
  FROM Superstore_test
  ) AS subquery
WHERE subquery.sales = Superstore_test.sales
;

实际执行结果：

原理层面的问题解释 #

1. 子查询逻辑无误：子查询中通过LEAD(sales) OVER (ORDER BY order_id asc)，已经按order_id升序正确计算出每行对应的下一行销售额，单独执行时结果正确是合理的。
2. UPDATE的关联条件完全错误：
   • 你使用subquery.sales = Superstore_test.sales作为关联条件，这意味着原表中的某一行会匹配子查询里所有sales值相同的行。数据库处理这种一对多关联时，会随机选取其中一个匹配行的next_sales赋值给原表行，直接导致结果混乱。
   • 更关键的是，这个关联条件完全脱离了原本的排序逻辑：子查询是基于order_id排序生成的next_sales，但你却用非唯一的sales值来关联，根本无法保证原表行和子查询行的对应关系。
3. 正确的关联逻辑：必须用表的唯一标识（比如order_id）来关联原表和子查询，因为order_id能确保原表的每一行精准对应子查询中同一order_id的行，从而拿到正确的next_sales值。

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