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, Dec 23 '12 at 21:45
So, you're basically looking for a running total like [this](http://stackoverflow.com/questions/860966/calculate-a-running-total-in-sqlserver) 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, Dec 23 '12 at 21:53
Much clearer if you provide example data and desired results. — Martin Smith, Dec 23 '12 at 22:47
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, Dec 24 '12 at 01:47
@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, Dec 24 '12 at 10:30
@Martin - My poor example is at fault. It didn't quite have the intricacies of my actual table (which only adds the previous row to the current row if a certain condition exists). I was, however, able to adapt your answer to fit my particular need. I used this: (ORDER BY Row ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS RunningTotal to grab the previous row's value. I then was able to check for my condition and add quite easily. Thanks! — Anthony Tyler, Dec 24 '12 at 20:20

Martin Smith · Accepted Answer · 2012-12-23T22:46:18.930

3

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

edited Dec 23 '12 at 22:46

answered Dec 23 '12 at 21:57

Martin Smith

438,706
87
741
845

curious to know how you figured out the results OP wanted? Coz Field 1 doesn't have any values in OP's question :) – bonCodigo Dec 23 '12 at 22:00
The question text says they are all zero. Might be premature to claim that I have figured out the results they want though(!) but it gives a starting point. – Martin Smith Dec 23 '12 at 22:01
This is a lot more efficient that my answer, and easy enough to adapt if I've got the right result set. I didn't read it as row 1 field 1 being updated, through that may well be what is required. – Laurence Dec 23 '12 at 22:13
@Laurence - I tried to read between the lines a bit as to what the OP was actually after. Unfortunately the OP seems to have gone AWOL instead of sticking around to clarify though. I agree yours answers the question as stated more literally though in that they never mentioned updating `row1,field1` (+1) but I'm assuming that is likely an oversight. – Martin Smith Dec 23 '12 at 22:17
Row 2 is processed, field 1 is changed, then row 3 is processed using the new value from row 2 field1. – Anthony Tyler Dec 24 '12 at 02:13

score 1 · Answer 2 · edited May 23 '17 at 12:12

Something like this adapted from TSQL A recursive update?

With cte As (
  Select
    Row, 
    Field1, 
    Field2
  From
    t
  Where
    Row = 1
  Union All
  Select
    t.Row,
    t.Field2 + c.Field1,
    t.Field2
  From
    t
      Inner Join
    cte c
      On t.Row = c.Row + 1
)
Update
  t
Set
  Field1 = c.Field1
From
  t
    inner join
  cte c
    On t.Row = c.Row

http://sqlfiddle.com/#!6/cf843/1

Recursive SQL query - using results from query within query

2 Answers2