SQL field = other field minus an other ROW!

Question

Table has 2 cols: [nr] and [diff]

diff is empty (so far - need to fill)

nr has numbers:

 1
 2
 45
 677
 43523452

on the diff column i need to add the differences between pairs

 1 | 0
 2 | 1
 45 | 43
 677 | 632
 43523452 | 43522775

so basically something like :

 update tbl set diff = @nr - @nrold where nr = @nr

but i don't want to use fetch next, because it's not cool, and it's slow (100.000) records

how can I do that with one update?

Answer 1

try this -

update tablename
set diff = cast(nr as INT) - cast((select nr from tablename where diff is not null and nr = a.nr) as INT)
from tablename a
where diff is null

This is assuming you only have one older row for nr old in the table. else the subquery will return more than one value

Answer 2

CREATE TABLE #T(nr INT,diff INT)

INSERT INTO #T (nr) SELECT 1 
              UNION SELECT 2 
              UNION SELECT 45 
              UNION SELECT 677 
              UNION SELECT 43523452

;WITH cte AS
(
SELECT nr,diff, ROW_NUMBER() OVER (ORDER BY nr) RN
FROM #T
)
UPDATE c1
SET diff = ISNULL(c1.nr - c2.nr,0)
FROM cte c1
LEFT OUTER JOIN cte c2 ON c2.RN+1= c1.RN

SELECT nr,diff FROM #T

DROP TABLE #T

Answer 3

Have a look at something like this (full example)

DECLARE @Table TABLE(
        nr INT,
        diff INT
)

INSERT INTO @Table (nr) SELECT 1 UNION ALL
SELECT 2  UNION ALL
SELECT 45  UNION ALL
SELECT 677  UNION ALL
SELECT 43523452 

;WITH Vals AS (
        SELECT  nr,
                diff,
                ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) xID
        FROM    @Table
)
UPDATE  c
SET     diff = c.nr - ISNULL(p.nr, 0)
FROM    Vals c LEFT JOIN
        Vals p  ON  c.xID = p.xID + 1


SELECT  *
FROM    @Table