SQL update next row in select I have ran into a problem with SQL. I have a list with car and mileage data. What I need to do is for the empty fields to calculate data.

Each row has a field for KM (mileage in KM metric unit), prevMileage (mileage of previous row) and nextMileage (mileage of the next row). The prev and next fields are calculated in a list before this one. The problem with this is that if the KM for a row is NULL the prevmileage of the next row and the nextmileage of the previous row will be NULL as well.

To make my calculation correct I need to add the previous mileage to the calculated value I make for each row. But if there are multiple NULL rows after each other, this is impossible.

What I need now is a way to update the 'previousmileage' field of the next row. 

This is the code I have so far and a preview of the table:

Date      |     KM     |   prevmileage    | nextmpileage
--------------------------------------------------------
01/01/2008|     5000   |       4500       |     55000


Code

    , CalculatedList as (
SELECT
    PNL.MonthDate,
    PNL.FK_CarID,
    PNL.AllocationID,
    PNL.CO2,
    PNL.KM,
    PNL.beginMonth ,
    (CASE   WHEN PNL.KM IS NULL 
            THEN (CASE  WHEN PNL.PrevMileage IS NULL
                        THEN (CASE  WHEN PNLnext.PrevMileage IS NULL --Here I want to update the prevmilage of the next row
                                    THEN (CASE  WHEN PNL.NextMileage IS NULL
                                            THEN 0
                                            ELSE ((PNL.NextMileage / DATEDIFF(DAY, PNL.PrevMonthDate, PNL.NextMonthDate)) * DATEDIFF(DAY, PNL.PrevMonthDate, PNL.MonthDate))
                                            END) 
                                    ELSE 0
                                    END)
                        ELSE (CASE  WHEN PNL.NextMileage IS NULL
                                    THEN PNL.PrevMileage
                                    ELSE (CASE  WHEN PNL.PrevMileage = 0 OR PNL.NextMileage = 0
                                                THEN (CASE  WHEN PNL.NextMileage = 0
                                                            THEN 0
                                                            ELSE (PNL.NextMileage / 2)
                                                            END)
                                                ELSE ((PNL.NextMileage - PNL.PrevMileage) / DATEDIFF(DAY, PNL.NextMonthDate, PNL.PrevMonthDate)) * DATEDIFF(DAY, PNL.MonthDate, PNL.PrevMonthDate)
                                                END)
                                    END) 
                        END)
            ELSE PNL.KM
            END ) as Calculated,
    PNL.NextMileage,
    PNL.PrevMileage,
    PNL.PrevMonthDate,
    PNL.NextMonthDate,
    (CASE   WHEN PNL.KM IS NULL 
            THEN 'N'
            ELSE 'Y'
            END) AS RealData,
    PNLnext.PrevMileage as test
FROM
    PrevNextFullList as PNL outer apply
     (select top 1 ml2.*
      from PrevNextFullList ml2
      where ml2.FK_CarID = PNL.FK_CarID and
            ml2.MonthDate &gt; PNL.MonthDate
      order by ml2.MonthDate desc
     ) PNLnext
)
Do you actually need to persist the prev and next columns in the data? Why not use Window functions on retrieval of data to get what you need from your base table? I've made a bit of a guess on what your schema looks like in it's most basic form but this should help you. If not post CREATE statements for all tables involved and I'll edit the answer.

SELECT  MonthDate
        , CarId
        , KM
        , LAG(KM) OVER (PARTITION BY CarId ORDER BY MonthDate) Prev
        , LEAD(KM) OVER (PARTITION BY CarId ORDER BY MonthDate) [next]


FROM    PrevNextFullList


If you do need to persist the columns then use this statement to create a VIEW

UPDATE You would need to upgrade to SQL 2012 to do this though.