Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2002
    Location
    Singapore
    Posts
    71

    Unanswered: Fetch & Update Next Row

    Hello All SQL Experts.

    I am on MS SQL 2000. Have copied below script from SQL Server Books Online and modified to use with my table.

    DECLARE @ColA1_Content varchar(50)

    DECLARE ColA1_cursor CURSOR FOR
    SELECT ColA1 FROM VN00_SalesData_WorkTable
    ORDER BY Counter

    OPEN ColA1_cursor

    FETCH NEXT FROM ColA1_cursor INTO @ColA1_Content

    WHILE @@FETCH_STATUS = 0
    BEGIN
    PRINT @ColA1_Content

    FETCH NEXT FROM ColA1_cursor
    INTO @ColA1_Content
    END

    CLOSE ColA1_cursor
    DEALLOCATE ColA1_cursor



    I would like to update the next record with blank ColA1 field with the previous filled content of ColA1. Please see attached file for details.

    Please advise how the script should be modified to do this. Thank you very much.


    Best regards
    Teck Boon
    Attached Thumbnails Attached Thumbnails Fetch_And_Update_Blank.bmp  

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Gee I would declare a variable that held the last non blank or non null value and I would maintain that in my loop. also in my loop I would update the current value with a UPDATE statement if it is null or empty.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Code:
    DECLARE @Id INT
    DECLARE @ColA1_Content varchar(50)
    DECLARE @PreviousColA1_Content varchar(50)
    
    DECLARE ColA1_cursor CURSOR FOR
    SELECT id, [name]
    FROM asset
    ORDER BY Id
    
    OPEN ColA1_cursor
    
    SET @PreviousColA1_Content = NULL
    FETCH NEXT FROM ColA1_cursor INTO @Id, @ColA1_Content
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
    	IF @ColA1_Content IS NULL 
    		UPDATE asset 
    		SET [name] = @PreviousColA1_Content
    		WHERE Id = @Id
    		
    	FETCH NEXT FROM ColA1_cursor INTO @Id, @ColA1_Content
    END
    
    CLOSE ColA1_cursor
    DEALLOCATE ColA1_cursor
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    @Wim: what if two records "in a row" have a NULL value
    George
    Home | Blog

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    DECLARE @x table (
       id   int
     , cola char(1)
    )
    
    INSERT INTO @x (id, cola) VALUES (1, 'a')
    INSERT INTO @x (id, cola) VALUES (2, NULL)
    INSERT INTO @x (id, cola) VALUES (3, 'b')
    INSERT INTO @x (id, cola) VALUES (4, 'c')
    INSERT INTO @x (id, cola) VALUES (5, NULL)
    INSERT INTO @x (id, cola) VALUES (6, NULL)
    INSERT INTO @x (id, cola) VALUES (7, NULL)
    
    INSERT INTO @x (id, cola) VALUES (9, 'd')
    
    
    
    SELECT b.id
         , b.cola
         , c.cola
         , Coalesce(b.cola, c.cola) As result
    FROM   (
            SELECT a.id
                 , a.cola
                 , (SELECT Max(id) FROM @x WHERE cola IS NOT NULL AND id < a.id) As matching_id
            FROM   @x As a
           ) As b
     LEFT
      JOIN @x As c
        ON c.id = b.matching_id
    
    UPDATE a
    SET    cola = c.cola
    FROM   @x As a
     INNER
      JOIN (
            SELECT a.id
                 , a.cola
                 , (SELECT Max(id) FROM @x WHERE cola IS NOT NULL AND id < a.id) As matching_id
            FROM   @x As a
           ) As b
        ON b.id = a.id
     LEFT
      JOIN @x As c
        ON c.id = b.matching_id
    WHERE  a.cola IS NULL
    
    SELECT id
         , cola
    FROM   @x
    George
    Home | Blog

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Assuming that Counter defines your order, I'd use:
    Code:
    UPDATE VN00_SalesData_WorkTable
       SET ColA1 = (SELECT A.ColA1
          FROM VN00_SalesData_WorkTable AS A
          WHERE A.[Counter] = (SELECT Max(B.[Counter])
             FROM VN00_SalesData_WorkTable AS B
             WHERE  B.[Counter] < VN00_SalesData_WorkTable.[Counter]
                AND B.ColA1 IS NOT NULL))
       WHERE VN00_SalesData_WorkTable.ColA1 IS NULL
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Jun 2002
    Location
    Singapore
    Posts
    71

    Thank You.

    Hello All.

    Thank you so much for your kind advises. It works now.


    Best regards
    Teck Boon

Posting Permissions

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