Why does this select query provide the desired result but when modifed to an update it does not?-CodePudding

I'm trying to update a table to correct an error in the data that was loaded into SQLite. This is an example of the issue.

A code was skipped in arranging the data to be loaded, such that, beginning at indexInter = 4, the column "code" is one row out-of-sync (behind) with the rest of the data in each row. If I run the example select query below, the results are what is needed; but, when that query is modified to an update, all rows are updated to the first matching row.

Woudl you please tell me what novice error I am making and fundamental principle I am overlooking? Thank you.

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;

Got it to work using the following but why doesn't it work using the update above?

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

CodePudding user response：

In your 2nd query, the CTE is executed only once returning the rows that you expect from the initial rows of the table.

But in your 1st query, the CTE is executed for every row of the table that needs to be updated, because of the correlated subquery:

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

This means that after the 1st row is updated, the CTE is executed again but this time it selects the table where 1 row was updated.
Then it is executed again and it selects from the table where 2 rows were updated and so on.