MS SQL Server - update row value to next row with row id

Question

I have a table like below:

Newid1       a   b   c
   1       null  40  50
   2       null  45  55
   3       null  46  56
   4       null  47  57
   5       null  48  58

Now I want to update value in a column by updating C column value into it (For example: 50 to the second row, 55 to the third row, 56 to the fourth row ...). How can I do this with WHILE command. Thanks all in advance.

Answer 1

It can be done in one set based operation
Using a WHILE risks invoking the "Halloween problem" as you update rows over and over.

It's easier with SQL Server 2012 using LAG

DECLARE @t TABLE
(
    Newid1 int
    , a int  NULL
    , b int
    , c int
);

INSERT INTO @t VALUES(1, NULL, 40, 50)
        ,(2, NULL, 45, 55) ,(3, NULL, 46, 56)
        ,(4, NULL, 47, 57) ,(5, NULL, 48, 58);

-- SQL Server 2012
WITH t AS
(
    SELECT a, LAG(c) OVER (ORDER BY Newid1) AS nextC
    FROM @t
)
UPDATE t SET a = nextC;

SELECT * FROm @t;

But for SQL Server 2005+

DECLARE @t TABLE
(
    Newid1 int
    , a int  NULL
    , b int
    , c int
);

-- SQL Server 2005+
WITH t AS
(
    SELECT Newid1, ROW_NUMBER() OVER (ORDER BY Newid1) +1 AS nextID, a, c
    FROM @t
)
UPDATE t1
SET a = t2.c
FROM
   t AS t1
   JOIN
   t AS t2 ON t1.Newid1 = t2.nextID;

SELECT * FROM @t;

Answer 2

Sorry, no WHILE, just shift on 1 by ROW_NUMBER if you are using SQL Server 2005 or later

DECLARE @t TABLE
(
    Newid1 int
    , a int  NULL
    , b int
    , c int
);

INSERT INTO @t VALUES(1, NULL, 40, 50)
INSERT INTO @t VALUES(2, NULL, 45, 55)
INSERT INTO @t VALUES(3, NULL, 46, 56)
INSERT INTO @t VALUES(4, NULL, 47, 57)
INSERT INTO @t VALUES(5, NULL, 48, 58)


UPDATE t
   SET a = c.c
FROM @t t
INNER JOIN 
(
    SELECT
     a.NewId1
     ,b.c
    FROM ( SELECT id = ROW_NUMBER ( ) OVER (ORDER BY Newid1) + 1, * FROM @t) b    
        LEFT JOIN ( SELECT id = ROW_NUMBER ( ) OVER (ORDER BY Newid1), * FROM @t) a
            ON a.Id = b.Id
) c
ON c.NewId1 = t.NewId1

SELECT  * FROM @t