CODEWITHSUNDEEP
sqlsql-server
MattDevs
MattDevs

Reputation: 59

Update every row with data from row above

I would like to update every row of my table with values from row above depends on some values.

Let's say that my table looks like this:

  |storeID|   Date   | Stock  | Medition | Regularized |
    --------------------------------
    |   1   |2019-01-01|  500   |   550    |      1      |
    |   1   |2019-01-02|  500   |    0     |      0      |
    |   1   |2019-01-03|  500   |    0     |      0      |
    |   2   |2019-01-01|  250   |   300    |      1      |
    |   2   |2019-01-02|  250   |   350    |      0      |
    |   2   |2019-01-03|  250   |    0     |      0      |

And output has to look like this:

|storeID|   Date   | Stock  | Medition | Regularized |
--------------------------------
|   1   |2019-01-01|  500   |   550    |      1      |
|   1   |2019-01-02|  550   |    0     |      0      |
|   1   |2019-01-03|  550   |    0     |      0      |
|   2   |2019-01-01|  250   |   300    |      1      |
|   2   |2019-01-02|  300   |   350    |      0      |
|   2   |2019-01-03|  300   |    0     |      0      |

When a store has been regularized, its stock must be the amount of the medition in next day.
Sometimes a medition has been made but stock hasn't to be regularized. I tried some things with recursive tables and read a few post with similar issues (Update record with previous row) but still i'm not able to solve my problem.

**Imagine that i have a tank full of fuel. Every day I have sales and inputs and the stock of the tank goes changing. Any day i can do a medition to check if the real stock (medition) matches with the stock i wrote in the "books" (stock) If it's different and i check as 1 last column (Regularized), the stock next day will be the medition i did the day before. IF i check as 0 last column, the stock will be the same than day before even if i did a medition.

Thanks for every help you could provide me.

Upvotes: 2

Views: 166

Answers (2)

kiran gadhe
kiran gadhe

Reputation: 743

Assuming there is only one regularized row for each storeid 

UPDATE INPUT_TABLE IT  
SET STOCK = ( SELECT MEDICATION FROM INPUT_TABLE ITI WHERE IT.STOREID = ITI.STOREID AND ITI.Regularized =1 )
WHERE IT.Regularized =0

Hope this helps

Upvotes: 0

Luis Cazares
Luis Cazares

Reputation: 3585

You could just use CROSS APPLY to join the table to itself.

UPDATE st SET
    stock = u.Medition
FROM SomeTable st
CROSS APPLY( SELECT TOP 1 Medition 
            FROM SomeTable i
            WHERE i.storeID = st.storeID
            AND i.[Date] <= st.[Date]
            AND i.Regularized = 1
            ORDER BY [Date] DESC) u
WHERE st.Regularized = 0;

Upvotes: 1

Related Questions