Search code examples
sqlt-sql

SQL updating column with a difference value of current and previous row

I have a table that that contains cumulative rain fall data. I want to calculate the rain fall for each record and update the table with that value.

The table looks like this:

ID date WsRaincount Rain
1 2023-07-01 13:01 10
2 2023-07-01 13:05 10.2
3 2023-07-01 13:10 10.3

The result should be :

ID date WsRaincount Rain
1 2023-07-01 13:01 10 0
2 2023-07-01 13:05 10.2 .2
3 2023-07-01 13:10 10.3 .1

I can calculate the the difference using the LAG() function:

SELECT  
    ([wsraincount]) - (LAG([wsraincount]) OVER (ORDER BY [id])) AS RainDiff

My problem is how do I now take the RainDiff value and place it in the proper rain column?

Solution

  • You can use an updatable derived table or CTE (and update it directly, no need to rejoin).

    UPDATE WithDiff
SET Rain = RainDiff
FROM (
    SELECT *,
      wsraincount - LAG(wsraincount) OVER (ORDER BY id) AS RainDiff
    FROM yourTable t
) WithDiff;