Results 1 to 5 of 5

Thread: update rownum

  1. #1
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11

    Unanswered: update rownum

    I have a problem with a specific query and I know there are some sql special-x that spot this forum.
    I have a simple table and can get the rownum for rows
    sample query below with output

    select a.rownum ,b.id,b.seq_number from batch.message b, ( select id,ROW_NUMBER() OVER (ORDER BY (CASE WHEN FORM='T820' THEN 1 ELSE 0 END), ID ASC) ROWNUM FROM BATCH.MESSAGE) a where a.id=b.id

    ROWNUM ID SEQ_NUMBER
    -------------------- -------------------- --------------------
    1 1 -
    2 2 -
    3 3 -
    4 4 -
    10 5 -
    11 6 -
    12 7 -
    13 8 -
    5 9 -
    6 10 -
    7 11 -
    8 12 -
    9 13 -

    the problem now is that we want to update the column seq_number with the value of rownumber of previous select by id
    or I am getting always 1 as result or I always get and error indicating the select returns more than 1 row
    any help is welcome..
    thanks for all update/help
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    It is better to publish before data of batch.message and CREATE TABLE statement of batch.message.
    The values of form column are not known for me and other than you.

    Anyway, try this...
    Example 1:
    Code:
    UPDATE
    (SELECT id
          , seq_number
          , ROW_NUMBER()
               OVER( ORDER BY CASE
                              WHEN FORM = 'T820' THEN
                                   1
                              ELSE 0
                              END
                            , ID ASC ) ROWNUM
      FROM  batch.message
    )
    SET seq_number = rownum
    ;
    Note: I tested the update statement on DB2 9.7.5 on Windows/XP
    using some guess for columns of batch.message and values of form column.
    It might not work on some other environment.
    Last edited by tonkuma; 12-23-11 at 08:42.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Test data:
    Code:
    ------------------------------ Commands Entered ------------------------------
    CREATE TABLE batch.message
    ( id         INTEGER NOT NULL
    , seq_number INTEGER
    , form       CHAR(4)
    );
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    INSERT INTO batch.message
    (id , form)
    VALUES
      (  1 , 'T860' )
    , (  2 , 'T850' )
    , (  3 , 'T840' )
    , (  4 , 'T830' )
    , (  5 , 'T820' )
    , (  6 , 'T820' )
    , (  7 , 'T820' )
    , (  8 , 'T820' )
    , (  9 , 'T810' )
    , ( 10 , 'T790' )
    , ( 11 , 'U810' )
    , ( 12 , 'U850' )
    , ( 13 , 'S820' );
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    SELECT *
     FROM  batch.message;
    ------------------------------------------------------------------------------
    
    ID          SEQ_NUMBER  FORM
    ----------- ----------- ----
              1           - T860
              2           - T850
              3           - T840
              4           - T830
              5           - T820
              6           - T820
              7           - T820
              8           - T820
              9           - T810
             10           - T790
             11           - U810
             12           - U850
             13           - S820
    
      13 record(s) selected.
    batch.message after updated by Example 1:
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT *
     FROM  batch.message;
    ------------------------------------------------------------------------------
    
    ID          SEQ_NUMBER  FORM
    ----------- ----------- ----
              1           1 T860
              2           2 T850
              3           3 T840
              4           4 T830
              5          10 T820
              6          11 T820
              7          12 T820
              8          13 T820
              9           5 T810
             10           6 T790
             11           7 U810
             12           8 U850
             13           9 S820
    
      13 record(s) selected.

  4. #4
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    understood --- many many thanks..
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Tried to make the query shorter.

    Example 2:
    Code:
    ------------------------------ Commands Entered ------------------------------
    UPDATE (
    SELECT seq_number
         , ROW_NUMBER() OVER (
              ORDER BY POSSTR(form , 'T820') , id
           ) rownum
     FROM  batch.message
    )
    SET seq_number = rownum
    ;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    SELECT *
     FROM  batch.message;
    ------------------------------------------------------------------------------
    
    ID          SEQ_NUMBER  FORM
    ----------- ----------- ----
              1           1 T860
              2           2 T850
              3           3 T840
              4           4 T830
              5          10 T820
              6          11 T820
              7          12 T820
              8          13 T820
              9           5 T810
             10           6 T790
             11           7 U810
             12           8 U850
             13           9 S820
    
      13 record(s) selected.
    Last edited by tonkuma; 12-23-11 at 16:32.

Posting Permissions

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