Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2002
    Posts
    227

    Unanswered: help on update progressive!!!!

    Hi
    I've table TAB_PROG with 1000 record but only with 8 progressive:

    ID_NUM..........MY_DATE...............PROGRESSIVE
    1.................10/01/2006.................1
    2.................12/01/2006.................2
    20................13/01/2006.................3
    34................15/01/2006.................4
    44................17/01/2006.................5
    55................18/01/2006.................6
    67................19/01/2006.................7
    77................20/01/2006.................8
    49............................................
    89...........................................
    90...........................................
    98...........................................
    111..........................................
    222..........................................


    I'd like to increment the column progressive by 1, starting from max value (=8)

    ID_NUM..........MY_DATE...............PROGRESSIVE
    1.................10/01/2006.................1
    2.................12/01/2006.................2
    20................13/01/2006.................3
    34................15/01/2006.................4
    44................17/01/2006.................5
    55................18/01/2006.................6
    67................19/01/2006.................7
    77................20/01/2006.................8

    49...........................................9
    89...........................................10
    90...........................................11
    98...........................................12
    111..........................................13
    222..........................................14
    ...............................................
    ...............................................
    ...............................................
    ...............................................
    ...............................................

    I tried this:

    update tab_prog set progressive =
    (select prog from
    (select id_num,rownum prog from
    (select id_num from tab_prog order by id_num)
    ) rn where rn.id_num=tab_prog.id_num
    );

    but I don't want update also progressive that are already assigned.
    I'd like to start from max progressive.


    How can I UPDATE my table TAB_PROG to increment the column progressive by 1?

    Thanks in advance!

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    CREATE SEQUENCE seq START WITH 9;

    UPDATE tab_prog SET
    progressive = seq.nextval
    WHERE progressive IS NULL;

  3. #3
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    So, how do you define this "progressive" column exactly ? I mean, when progressive is null, how do you define what the order of the rest progressive should be ?
    Should it be like this
    Code:
    SQL> select id_num, my_date, row_number( ) over( order by rownum ) progressive
      2    from (
      3  select id_num, my_date
      4    from tab_prog
      5   where progressive is null
      6   order by dbms_random.value
      7         )
      8  /
    
        ID_NUM MY_DATE    PROGRESSIVE
    ---------- ---------- -----------
            49                      1
           222                      2
            90                      3
            98                      4
            89                      5
           111                      6
    
    6 rows selected.
    or perhaps like this..
    Code:
    SQL> /
    
        ID_NUM MY_DATE    PROGRESSIVE
    ---------- ---------- -----------
            98                      1
            90                      2
            49                      3
            89                      4
           222                      5
           111                      6
    
    6 rows selected.
    ..which is what is more likely to happen if you dont explicity especify an order..

Posting Permissions

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