为何SQLite SELECT查询符合预期，改写为UPDATE语句后结果异常？

我尝试更新SQLite中的一张表，修正数据加载时的错误——加载时漏写代码，导致从indexInter=4开始，code列和其余数据不同步（滞后一行）。执行SELECT查询时结果符合预期，但改成UPDATE后，所有符合条件的行都被更新成第一个匹配行的值。

表结构与初始数据 #

sqlite> .dump testUpdate
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE testUpdate (indexRow integer unique, indexInter integer, code text);
INSERT INTO testUpdate VALUES(130120390150,1,'H1961');
INSERT INTO testUpdate VALUES(130120390250,2,'H8033');
INSERT INTO testUpdate VALUES(130120390350,3,'H1732');
INSERT INTO testUpdate VALUES(130120390450,4,'H3117');
INSERT INTO testUpdate VALUES(130120390470,NULL,'punct3');
INSERT INTO testUpdate VALUES(130120390550,5,'H7969');
INSERT INTO testUpdate VALUES(130120390650,6,'H398');
INSERT INTO testUpdate VALUES(130120390750,7,'H8354');
INSERT INTO testUpdate VALUES(130120390770,NULL,'punct2');
INSERT INTO testUpdate VALUES(130120390850,8,'');
INSERT INTO testUpdate VALUES(130120390950,9,'H3559');
INSERT INTO testUpdate VALUES(130120391050,10,'');
INSERT INTO testUpdate VALUES(130120391150,11,'H251');
INSERT INTO testUpdate VALUES(130120391250,12,'');
COMMIT;

符合预期的SELECT查询结果 #

sqlite> with fixes as
 (select indexInter, code
  from testUpdate
  where indexInter > 2)
 select indexRow, indexInter, code, 
       (select code 
        from fixes
        where fixes.indexInter+1 = testUpdate.indexInter) as edit
 from testUpdate;
indexRow      indexInter  code    edit 
------------  ----------  ------  -----
130120390150  1           H1961        
130120390250  2           H8033        
130120390350  3           H1732        
130120390450  4           H3117   H1732
130120390470              punct3       
130120390550  5           H7969   H3117
130120390650  6           H398    H7969
130120390750  7           H8354   H398 
130120390770              punct2       
130120390850  8                   H8354
130120390950  9           H3559        
130120391050  10                  H3559
130120391150  11          H251         
130120391250  12                  H251 

错误的UPDATE语句及结果 #

sqlite> begin transaction;
sqlite> with fixes as 
  (select indexInter, code
   from testUpdate
   where indexInter > 2)
  update testUpdate
  set code = (select code
              from fixes
              where fixes.indexInter+1 = testUpdate.indexInter) 
  where indexInter > 3 
  returning *;
indexRow      indexInter  code 
------------  ----------  -----
130120390450  4           H1732
130120390550  5           H1732
130120390650  6           H1732
130120390750  7           H1732
130120390850  8           H1732
130120390950  9           H1732
130120391050  10          H1732
130120391150  11          H1732
130120391250  12          H1732
sqlite> rollback;

正确的解决写法及结果 #

sqlite> begin transaction;
sqlite> with fixes as
  (select indexInter, code
   from testUpdate
   where indexInter > 2)
  update testUpdate
  set code = fixes.code
  from fixes
  where fixes.indexInter+1 = testUpdate.indexInter
    and testUpdate.indexInter > 3
  returning *;
indexRow      indexInter  code 
------------  ----------  -----
130120390450  4           H1732
130120390550  5           H3117
130120390650  6           H7969
130120390750  7           H398 
130120390850  8           H8354
130120390950  9                
130120391050  10          H3559
130120391150  11               
130120391250  12          H251 
sqlite> select * from testUpdate;
indexRow      indexInter  code  
------------  ----------  ------
130120390150  1           H1961 
130120390250  2           H8033 
130120390350  3           H1732 
130120390450  4           H1732 
130120390470              punct3
130120390550  5           H3117 
130120390650  6           H7969 
130120390750  7           H398  
130120390770              punct2
130120390850  8           H8354 
130120390950  9                 
130120391050  10          H3559 
130120391150  11                
130120391250  12          H251  

错误原因分析 #

1. CTE未物化导致的动态查询问题
   最初的UPDATE语句中，fixes公共表表达式（CTE）没有被物化，SQLite会在每次更新行时重新执行fixes的查询逻辑。而UPDATE操作是逐行修改并立即生效的：当第一行（indexInter=4）被更新为H1732后，后续行查询fixes时，indexInter=4的code值已经变成了H1732，不再是原来的H3117。这就导致后续所有行都会匹配到indexInter=4的这条数据，最终全部被更新为H1732。

2. 两种写法的核心差异

   • 改用的UPDATE ... FROM ...写法，SQLite会先将fixes的数据作为临时表加载（相当于隐式物化），基于原始数据完成所有匹配后再批量执行更新，不会受到更新过程中数据变化的影响。
   • 给fixes添加materialized提示时，强制SQLite将CTE的结果物化到临时表中，查询时基于原始数据的快照进行匹配，同样避免了动态查询带来的错误。

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