Updating a column with a value from above doesnt work

  • Another natural option is the recursive one:

    DECLARE @Example

    TABLE (

    ID INTEGER PRIMARY KEY,

    Inv INTEGER NOT NULL,

    Est DECIMAL(9,1) NOT NULL,

    Diff DECIMAL(10,1) NOT NULL

    );

    INSERT @Example

    (ID, Inv, Est, Diff)

    VALUES (1, 1690, 182, 1508),

    (2, 1690, 52, 1638),

    (3, 1690, 408, 1282),

    (4, 1690, 265.2, 1424.8),

    (5, 1690, 600, 1090),

    (6, 1690, 18, 1672),

    (7, 1690, 18, 1672),

    (8, 1690, 92, 1598),

    (9, 1690, 4.8, 1685.2),

    (10, 1690, 50, 1640);

    WITH R

    AS (

    SELECT E.ID,

    E.Est,

    E.Inv - E.Est AS Diff

    FROM @Example AS E

    WHERE E.ID = 1

    UNION ALL

    SELECT E.ID,

    E.Est,

    CONVERT(DECIMAL(12, 1), R.Diff - E.Est)

    FROM R

    JOIN @Example E

    ON E.ID = R.ID + 1

    )

    UPDATE E

    SET Diff = R.Diff

    FROM R

    JOIN @Example E

    ON E.ID = R.ID;

Viewing post 16 (of 16 total)

You must be logged in to reply to this topic. Login to reply