How can update each row of a table based on two columns of it's previous row?-CodePudding

I have following table:

Id	offset	length
5000	0	5
5001	5	5
5002	10	4
5003	14	4
5010	23	5
5011	28	5

Offset value in each row is based on summation of offset and length of previous row. As you can see, 6 rows have been deleted between forth and fifth rows and I need to update again offset column based on regular way. My desired output would be as follow:

Id	offset	length
5000	0	5
5001	5	5
5002	10	4
5003	14	4
5010	18	5
5011	23	5

Is there a pure update SQL statement to achieve this in sqlite? I Appreciate any help.

CodePudding user response：

If your version of SQLite is 3.33.0 you can use the UPDATE ... FROM... syntax with SUM() window function:

UPDATE tablename AS t1
SET offset = t2.offset
FROM (
  SELECT Id, SUM(length) OVER (ORDER BY Id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) offset
  FROM tablename 
) AS t2
WHERE t2.Id = t1.Id AND t2.offset IS NOT NULL;

See the demo.

For previous versions use a correlated subquery:

UPDATE tablename AS t1
SET offset = COALESCE(
  (SELECT SUM(t2.length) FROM tablename t2 WHERE t2.Id < t1.Id), 
  t1.offset
);

See the demo.