none
update rows

    Question

  • Hi guys

    using sql server 2005

    I need help to update null columns with values, see the sample data, to fill null values... I need to check all the null rows in between not null values order by ord irrespective of dates...and update the null values with the value filled in top not null row which means need to update the null values from top to bottom..

    May be the description is little confusing that is why I provided the result sample which needs to be expected hopefully that will help you to provide me solution. 

    CREATE TABLE tt (id INT,ord int,dt1 datetime, wd int)

     
    INSERT INTO tt VALUES (102,'1','2013-05-27','544')
    INSERT INTO tt VALUES (102,'2','2013-06-02',NULL)
    INSERT INTO tt VALUES (102,'3','2013-06-03',NULL)
    INSERT INTO tt VALUES (102,'4','2013-06-10',NULL)
    INSERT INTO tt VALUES (102,'5','2013-07-08','690')
    INSERT INTO tt VALUES (102,'6','2013-07-10',NULL)
    INSERT INTO tt VALUES (102,'7','2013-07-10',NULL)
    INSERT INTO tt VALUES (102,'8','2013-07-11','511')
    INSERT INTO tt VALUES (102,'9','2013-07-11',NULL)

    select * From tt

    /******************************/

    CREATE TABLE tt_result (id INT,ord int,dt1 datetime, wd int)  
    INSERT INTO tt_result VALUES (102,'1','2013-05-27','544')
    INSERT INTO tt_result VALUES (102,'2','2013-06-02','544')
    INSERT INTO tt_result VALUES (102,'3','2013-06-03','544')
    INSERT INTO tt_result VALUES (102,'4','2013-06-10','544')
    INSERT INTO tt_result VALUES (102,'5','2013-07-08','690')
    INSERT INTO tt_result VALUES (102,'6','2013-07-10','690')
    INSERT INTO tt_result VALUES (102,'7','2013-07-10','690')
    INSERT INTO tt_result VALUES (102,'8','2013-07-11','511')
    INSERT INTO tt_result VALUES (102,'9','2013-07-11','511')

    select * From tt_result


    • Edited by leo_dec Thursday, February 20, 2014 2:52 PM
    Thursday, February 20, 2014 2:39 PM

Answers

  • May be the below:

    CREATE TABLE tt (id INT,ord int,dt1 datetime, wd int)
    
     
    INSERT INTO tt VALUES (102,'1','2013-05-27','544') 
    INSERT INTO tt VALUES (102,'2','2013-06-02',NULL) 
    INSERT INTO tt VALUES (102,'3','2013-06-03',NULL) 
    INSERT INTO tt VALUES (102,'4','2013-06-10',NULL) 
    INSERT INTO tt VALUES (102,'5','2013-07-08','690') 
    INSERT INTO tt VALUES (102,'6','2013-07-10',NULL) 
    INSERT INTO tt VALUES (102,'7','2013-07-10',NULL) 
    INSERT INTO tt VALUES (102,'8','2013-07-11','511') 
    INSERT INTO tt VALUES (102,'9','2013-07-11',NULL)
    
    update A set wd= 
    (Select Wd From tt where Ord =(Select MAX(Ord) From tt where A.ord>=tt.ord and A.id = tt.id and tt.wd IS not null))
    From tt A 
    
    Select* From tt
    Drop table TT

    Thursday, February 20, 2014 4:33 PM
  • hi

    thanks for your quick reply... could you please help me to write same query without using "WITH" functionality?

    regards

    check below code

    UPDATE t
    SET wd= t1.wd
    FROM tt t
    CROSS APPLY (SELECT TOP 1 wd
                 FROM tt
                 WHERE id = t.id
                 AND wd IS NOT NULL
                 ORDER BY id DESC)t1
    WHERE t.wd IS NULL


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Thursday, February 20, 2014 4:51 PM

All replies

  • Try the below:

    CREATE TABLE tt (id INT,ord int,dt1 datetime, wd int)
    
     
    INSERT INTO tt VALUES (102,'1','2013-05-27','544') 
    INSERT INTO tt VALUES (102,'2','2013-06-02',NULL) 
    INSERT INTO tt VALUES (102,'3','2013-06-03',NULL) 
    INSERT INTO tt VALUES (102,'4','2013-06-10',NULL) 
    INSERT INTO tt VALUES (102,'5','2013-07-08','690') 
    INSERT INTO tt VALUES (102,'6','2013-07-10',NULL) 
    INSERT INTO tt VALUES (102,'7','2013-07-10',NULL) 
    INSERT INTO tt VALUES (102,'8','2013-07-11','511') 
    INSERT INTO tt VALUES (102,'9','2013-07-11',NULL)
    
    ;With cte
    as
    (
    	Select *,wd as updwd From tt where ord=1
    	Union All
    	Select A.*,Case when A.wd is null then B.updwd else A.wd end  as updwd From tt A
    		Inner join cte B on A.id = B.id and A.ord = B.ord +1
    )update B Set B.wd= A.updwd From cte a
    Inner join tt B on A.id = B.id and a.ord = B.ord where B.wd is null
    
    Select * From tt
    
    Drop table TT

    Thursday, February 20, 2014 2:59 PM
  • hi

    thanks for your quick reply... could you please help me to write same query without using "WITH" functionality?

    regards

    Thursday, February 20, 2014 3:10 PM
  • May be the below:

    CREATE TABLE tt (id INT,ord int,dt1 datetime, wd int)
    
     
    INSERT INTO tt VALUES (102,'1','2013-05-27','544') 
    INSERT INTO tt VALUES (102,'2','2013-06-02',NULL) 
    INSERT INTO tt VALUES (102,'3','2013-06-03',NULL) 
    INSERT INTO tt VALUES (102,'4','2013-06-10',NULL) 
    INSERT INTO tt VALUES (102,'5','2013-07-08','690') 
    INSERT INTO tt VALUES (102,'6','2013-07-10',NULL) 
    INSERT INTO tt VALUES (102,'7','2013-07-10',NULL) 
    INSERT INTO tt VALUES (102,'8','2013-07-11','511') 
    INSERT INTO tt VALUES (102,'9','2013-07-11',NULL)
    
    update A set wd= 
    (Select Wd From tt where Ord =(Select MAX(Ord) From tt where A.ord>=tt.ord and A.id = tt.id and tt.wd IS not null))
    From tt A 
    
    Select* From tt
    Drop table TT

    Thursday, February 20, 2014 4:33 PM
  • hi

    thanks for your quick reply... could you please help me to write same query without using "WITH" functionality?

    regards

    check below code

    UPDATE t
    SET wd= t1.wd
    FROM tt t
    CROSS APPLY (SELECT TOP 1 wd
                 FROM tt
                 WHERE id = t.id
                 AND wd IS NOT NULL
                 ORDER BY id DESC)t1
    WHERE t.wd IS NULL


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Thursday, February 20, 2014 4:51 PM