Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2005
    Posts
    40

    Unanswered: Recursive Queries/Update statement

    Hi,
    Trying to update a single value within a table, thus eliminating nulls. Another words, if the value is NULL update it with the next preceeding non-null value. In this example, 1 should be CO, 2 should be CO, 6 should be CO, 8 should be TT, and 10 should be TT.

    For example,

    1 NULL
    2 NULL
    3 CO
    4 CO
    5 CO
    6 NULL
    7 TT
    8 NULL
    9 TT
    10 NULL

    Any ideas? Thanks.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd use:
    Code:
    --  ptp  20080522  See http://www.dbforums.com/showthread.php?t=1630594
    
    CREATE TABLE #foo ( -- Build a test table
       bar	    INT		NOT NULL
    ,  bat	    VARCHAR(5)	NULL
       )
    
    INSERT INTO #foo ( -- Populate with sample data
       bar, bat
       ) SELECT         1, NULL
          UNION SELECT  2, NULL
          UNION SELECT  3, 'CO'
          UNION SELECT  4, 'CO'
          UNION SELECT  5, 'CO'
          UNION SELECT  6, NULL
          UNION SELECT  7, 'TT'
          UNION SELECT  8, NULL
          UNION SELECT  9, 'TT'
          UNION SELECT 10, NULL
    
    SELECT * FROM #foo   -- Show we got it right so far
    
    UPDATE #foo
       SET bat = (SELECT z1.bat -- Update bat
          FROM #foo AS z1
          WHERE  z1.bar = (SELECT Min(z2.bar) -- Using closest bar
             FROM #foo AS z2
             WHERE  #foo.bar < z2.bar
                AND z2.bat IS NOT NULL)) -- with a non-NULL bat
       WHERE  bat IS NULL -- only for rows with a NULL bat
    
    SELECT * FROM #foo -- Show we got it right so far
    
    DROP TABLE #foo -- bye bye
    -PatP

  3. #3
    Join Date
    Apr 2005
    Posts
    40
    Pat thanks for the attempt. The last record is null, but your code gives me another idea/approach.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •