SQLite基于初始类别值范围更新行、避免级联修改无关行的实现方法

问题根因：SQLite执行UPDATE语句时，WHERE子句中的子查询默认读取的是更新过程中实时变动的表数据，而非更新前的快照，因此先被更新为参考类别的行会被后续匹配逻辑当作参考基准，导致级联误更新。

方案1：使用预计算CTE固定参考基准（兼容SQLite 3.29.0版本） #

CTE（公共表表达式）会在UPDATE执行前完成计算，固定为更新前就属于参考类别的行，避免后续更新的行干扰匹配逻辑。

WITH ref_rows AS (
    -- 提前查询更新前所有属于参考类别的行，结果固定不会随更新变动
    SELECT Value, Value2 
    FROM tablename 
    WHERE Category = 2
)
UPDATE tablename
SET Category = 2
WHERE Category != 2
  AND EXISTS (
        SELECT 1
        FROM ref_rows t
        WHERE tablename.Value BETWEEN t.Value - 4 AND t.Value + 4
          AND tablename.Value2 BETWEEN t.Value2 - 2 AND t.Value2 + 2
    );

如果业务要求目标类别与参考类别为同一个值、且需要覆盖更新参考类别的行，删除WHERE条件中的Category != 2即可。

方案2：高版本SQLite可使用UPDATE FROM写法（SQLite 3.33.0及以上支持） #

如果后续升级了SQLite版本，也可以使用性能更优的UPDATE FROM语法：

UPDATE tablename u
SET Category = 2
FROM tablename t
WHERE u.Category != 2
  AND t.Category = 2
  AND u.Value BETWEEN t.Value -4 AND t.Value +4
  AND u.Value2 BETWEEN t.Value2 -2 AND t.Value2 +2;

以上两种方案执行后，示例数据中仅id为6、8、11的行会被更新为Category=2，完全符合预期。

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