Results 1 to 6 of 6
  1. #1
    Join Date
    May 2014
    Posts
    3

    Unanswered: Update a column in a table

    Hi guys,

    I need to write a update statement which will update the same table with value from the same column but the values will be changing.

    For an example.
    The source table is as below.
    ROWID DATA Header
    1 AAA H1
    2 isa1
    3 isb2
    4 AAA3b3 H2
    5 same
    Now the records with rowid 2 and 3 should be updated with H1 as Header which is present in the Rowid 1.

    Similarly the record rowid 5 should be updated with H2 as header from rowid 4.

    I am having a lot of headers like this.

    Could somebody help me out with this.

    Cheers!!!!!

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Based on your example data, it looks like you already have what you want. I'm confused!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    May 2014
    Posts
    3
    Quote Originally Posted by Pat Phelan View Post
    Based on your example data, it looks like you already have what you want. I'm confused!

    -PatP
    I like to have the update statement which will update Header column of Rowid 2 and 3 with Header value of Rowid 1(H1) and similarly update Header column of Rowid 5 with Header value of Rowid 4(H2). I wrote the update statement but yet to test it.. Will this work?

    Code:
    UPDATE table_mike t1
      SET t1.header = (SELECT MAX (header)
                         FROM table_mike t2
                        WHERE t2.ROWID < t1.ROWID)

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    sounds like you want to update with the header from the MAX rowid that is less than the current rowid where your header is blank or null by joining the table to itself in your set statement? Hopefully that will point you in the right direction for your query.
    Dave

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    DECLARE @t TABLE (
       ROWID        INT
    ,  DATA         NVARCHAR(9)
    ,  Header       NVARCHAR(9)
    )
    
    INSERT INTO @t (ROWID, DATA, Header) VALUES 
       (1, 'AAA',    'H1'),  (2, 'isa1',   NULL)
    ,  (3, 'isb2',   NULL),  (4, 'AAA3b3', 'H2')
    ,  (5, 'same',   NULL)
    
    ; WITH cte (ROWID, DATA, Header) AS (
    SELECT ROWID, DATA, Header
       FROM @t
       WHERE  1 = ROWID
    UNION ALL SELECT b.ROWID, b.DATA
    ,  Coalesce(b.Header, a.Header) AS Header
       FROM cte AS a
       JOIN @t AS b
          ON b.ROWID = 1 + a.ROWID
    )
    SELECT ROWID, DATA, Header AS Header
       FROM cte
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    This example worked on DB2 9.7 on Windows.

    You might want to amend the example to conform the syntax of Microsoft SQL server.

    Example:
    Code:
    UPDATE
    (
    SELECT r.*
         , MAX(header)
              OVER(PARTITION BY max_rowid) AS max_header
     FROM  (SELECT s.*
                 , MAX(CASE WHEN header IS NOT NULL THEN rowid END)
                      OVER(ORDER BY rowid
                           ROWS BETWEEN UNBOUNDED PRECEDING
                                    AND CURRENT ROW
                          ) AS max_rowid
             FROM  source_table AS s
           ) AS r
    )
     SET   header = max_header
     WHERE header IS NULL
    ;

Posting Permissions

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