Post reply

Update Table by Replacing NULLS

  • October 13, 2005 at 10:04 am

    #91426

    Hi all,

     

    I am trying to create a table like this below. I need to replace the NULL values in the COL3 with the max. no. of col3 till that row. means I need to replace the NULLS where col1 = D, col1 = E; with 3. and the NULLs where COL1 = H,I should be replaced with 5.

     

    This is the table I created.

     

    COL1              COL2              COL3

    A                     1                      1

    B                      1                      2

    C                     1                      3

    D                     0                      NULL

    E                      0                      NULL

    F                      1                      4

    G                     1                      5

    H                     0                      NULL

    I                       0                      NULL 

    My Table supposed to look like this

    COL1              COL2              COL3

    A                     1                      1

    B                      1                      2

    C                     1                      3

    D                     0                      3

    E                      0                      3

    F                      1                      4

    G                     1                      5

    H                     0                      5

    I                       0                      5

    Any help will be appreciated.

  • October 13, 2005 at 10:27 am

    #597152

    DECLARE @OriginalTable TABLE( COL1 varchar(5),

                                                       COL2 integer,

                                                       COL3 integer)

    INSERT INTO @OriginalTable

    SELECT 'A', 1, 1          UNION ALL

    SELECT 'B', 1, 2          UNION ALL

    SELECT 'C', 1, 3          UNION ALL

    SELECT 'D', 0, NULL    UNION ALL

    SELECT 'E', 0, NULL    UNION ALL

    SELECT 'F', 1, 4          UNION ALL

    SELECT 'G', 1, 5          UNION ALL

    SELECT 'H', 0, NULL    UNION ALL

    SELECT 'I', 0, NULL 

    UPDATE @OriginalTable SET

              COL3 = (SELECT MAX( OT.COL3) FROM @OriginalTable OT

                            WHERE OT.COL3 IS NOT NULL

                             AND ASCII( OT.COL1) < ASCII( O.COL1))

    FROM @OriginalTable O

    WHERE O.COL3 IS NULL

    SELECT * FROM @OriginalTable

    I wasn't born stupid - I had to study.

  • October 13, 2005 at 11:00 am

    #597169

    Thanks a lot Farell, worked me great!

  • October 13, 2005 at 11:15 am

    #597177

    Glad to help. 

    I wasn't born stupid - I had to study.

  • October 14, 2005 at 11:47 am

    #597430

    You lost me Joe.  Your psuedo-code has no equal and has a missing paren. 

    "proprietary UPDATE... FROM.. syntax."?  Can you explain for me? 

    Thanks

     

    I wasn't born stupid - I had to study.

  • October 14, 2005 at 1:35 pm

    #597456

    That is why I used ASCII.  It did not matter which of the previous records was used, only that the MAX( Col3) WHERE the ASCII( Col1) was less than the value UPDATED. 

    I can see the point of the ORDER BY if the multiple values 'could' be chosen, but in this case, MAX fulfilled the requirment... 

    Thanks for the explanation, though.  That is important to keep in mind.   

     

    I wasn't born stupid - I had to study.

Viewing 6 posts - 1 through 5 (of 5 total)

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