Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

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;
share|improve this answer
1  
+1 for LAG. it was useful, thanks –  garik Feb 20 '12 at 8:28

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
share|improve this answer
    
Sorry, but what if I want to calculate the C column and update it to the a column of next row. I mean the value in C column is a dynamic value! You're very kind to help me with this answer. Thanks a lot! –  Teddy NGUYEN Feb 20 '12 at 8:19
    
@Teddy NGUYEN no problem, but it should be optimized with CTE clause to improve performance (double select on @t: row_number and row_number + 1) –  garik Feb 20 '12 at 8:23
    
Dear Garik, thank you so much for this support. BTW, if you don't mind, can you give me some more ways to contact you (email,IM...), because I'm a newbie of SQL, I must have had more questions that need your help. Terribly sorry if my request cause inconvenience to you! :) Best regards! –  Teddy NGUYEN Feb 20 '12 at 8:35
    
@Teddy NGUYEN just ask questions on this site. It does not depend knew you something before, any way it is a good chance to learn more. –  garik Feb 20 '12 at 12:52
    
what if I have the value of the A column at the first row (rowid =1) only and I want to use that value for calculating those next row. In my case, I updated the first row at column A with a static value 20. After that, I use this value for calculating: a(20) + b - c (20+40-50=10). The 10 value will be updated to the second row and calculate the result for the third row: (10 + 45 - 55 = 0), the 0 value will be updated to the third row at column A, and continue calculating (0 + 46 - 56 = -10), and more... Once again, thanks a lot! :-) –  Teddy NGUYEN Feb 20 '12 at 14:53

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.