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?
Note also that the above version will work if the fixes table is materialized using with fixes as materialized. When this hint is not provided, this documentation at SQLite site under the heading "Materialization Hints" explains how SQLite determines whether or not to materialize the table or run it as a subquery for each row of the UPDATE. This question on the SQLite forum describes it in greater detail.
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
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.