Recursive SQL query - using results from query within query

Question

I'm running SQL Server 2012, and here's what I need:

Row      Field1          Field2
 1         0               1
 2         ?               2
 3         ?               -5

I need a query that will go throw row by row. It should take row2,field1 and set it equal to row1,field1+row2,field2

It then would take row3,field1 and set it equal to row2,field1+row3,field2

Initially the table has values in Field1 that are all equal to 0, and so when I run my query it just always uses 0 for the field1 values.

Any help would be appreciated. I was thinking a CTE would be the way to go, but I just don't know where to go with that.

Edit: Just to clear up some things, in my example. The initial input would be

Row      Field1          Field2
 1         0               1
 2         0               2
 3         0               -5

The desired output would be:

Row      Field1          Field2
 1         1               1
 2         3               2
 3         -2               -5

My actual table is a bit complicated, but I know I can apply it specifically if I could understand how to pull it off with this example.

Is the shown table your input, if so can you show us your expected results as well? — bonCodigo
So, you're basically looking for a running total like this but slightly weirder? I would recommend against using a recursive CTE on an entire table in this manner as it will involve as many index/table scans as you have rows. — Ben
Much clearer if you provide example data and desired results. — Martin Smith
I edited to show some example input/output. It's kind of like the running total, but it only adds things from the previous row and the current row. The actual data set is quite large (MLB play by play data) — Anthony Tyler
@AnthonyTyler - But if it is only adding things from the previous row and the field from the previous row that is adding has in turn been updated from its previous row it is exactly a running total isn't it? I don't see any difference in your desired results and the results in the first method in my answer. Maybe update your example so it shows the difference? — Martin Smith

Martin Smith Martin Smith · Accepted Answer · 2012-12-23T21:57:54

Is this what you need? (Unclear if when you refer to row2,field1 for example you mean the before or after update value)

CREATE TABLE YourTable
  (
     Row    INT,
     Field1 INT NULL,
     Field2 INT
  )

INSERT INTO YourTable
VALUES      (1,0,1),
            (2,0,2),
            (3,0,-5); 
WITH CTE AS
(
SELECT *,
       SUM(Field2) OVER (ORDER BY Row ROWS UNBOUNDED PRECEDING) AS RunningTotal
FROM  YourTable    
)
UPDATE CTE 
SET Field1 = RunningTotal

SELECT *
FROM YourTable

Final Result

Row         Field1      Field2
----------- ----------- -----------
1           1           1
2           3           2
3           -2          -5

Or another (more literal) interpretation of your word problem might be

WITH CTE AS
(
SELECT *,
       LAG(Field2) OVER (ORDER BY Row) AS PrevRowField2
FROM  YourTable    
)
UPDATE CTE 
SET Field1 = PrevRowField2 + Field1
WHERE PrevRowField2 IS NOT NULL

Recursive SQL query - using results from query within query

2 Answers