为什么这个select查询提供所需的结果，但当修改为更新时却没有?

Question

我正在尝试更新表以纠正加载到SQLite中的数据中的错误。这是该问题的一个例子。

在安排要加载的数据时跳过了一个代码，这样，从indexInter = 4开始，列"code"一行与每行中的其他数据不同步(落后)。如果我运行下面的示例select查询，结果就是所需要的;但是，当该查询被修改为更新时，所有行都更新为第一个匹配行。

你能告诉我我犯了什么新手错误，我忽略了什么基本原则吗?谢谢你。

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;
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 
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;

让它工作使用以下，但为什么它不工作使用上面的更新?

还请注意，如果使用with fixes as materialized实现修复表，则上述版本将正常工作。当没有提供这个提示时，这个文档在SQLite站点的"物化提示"标题下。解释SQLite如何确定是将表具体化还是将其作为UPDATE的每一行的子查询运行。这个问题在SQLite论坛上有更详细的描述。

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  

Answer 1

在第二个查询中，CTE只执行一次，返回您期望从表的初始行中得到的行。

但是在您的第一个查询中，由于相关子查询

，对需要更新的表的每一行执行CTE:

select code from fixes where fixes.indexInter+1 = testUpdate.indexInter

这意味着在第一行更新之后，再次执行CTE，但这次它选择更新了1行的表。
然后再次执行，从更新了2行的表中进行选择，以此类推。