Update current row from the results from previous row

Database

Update current row from the results from previous row

can someone help me to solve this problem with teradata update query?

Before Update:

Tablename : TEST_SCENARIO





Seq_No NUMBER SUM_FROM_PREV_REC
1 5 1
     
2 8  
     
3 33  
     
4 22  
     
5 45  
     
6 23  
     
7 10  

After update:

Tablename : TEST_SCENARIO







Seq_No NUMBER SUM_FROM_PREV_REC   Math ( previous row SUM_FROM_PREV_REC + previous row NUMBER)
1 5 1    
         
2 8 6   1+5
         
3 33 14   6+8
         
4 22 47   14+33
         
5 45 69   47+22
         
6 23 114   69+45
         
7 10 137   114+23
2 REPLIES

Re: Update current row from the results from previous row

readable format:

can someone help me to solve this problem with teradata update query?

Before Update:

Tablename : TEST_SCENARIO

Seq_No   NUMBER  SUM_FROM_PREV_REC

1             5                       1

2             8 

3             33 

4             22 

5             45 

6             23 

7             10 

After Update:

Tablename : TEST_SCENARIO

Seq_No NUMBER   SUM_FROM_PREV_REC   Math ( previous row SUM_FROM_PREV_REC + previous row NUMBER)

1               5             1                             

2               8             6                              1+5

3               33            14                             6+8

4               22            47                            14+33

5               45            69                            47+22

6               23            114                            69+45

7               10            137                            114+23

Junior Contributor

Re: Update current row from the results from previous row

Calculate the values in an UPDATE FROM:

SELECT seq_no, num, SUM_FROM_PREV_REC,
COALESCE(SUM(SUM_FROM_PREV_REC)
OVER (ORDER BY seq_no
ROWS UNBOUNDED PRECEDING),0) +
COALESCE(SUM(num)
OVER (ORDER BY seq_no
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0)
FROM tab