DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: help on update progressive!!!!

Hybrid View

  1. #1
    Join Date
    Jul 2002
    Posts
    228

    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
    Dec 2001
    Location
    UK
    Posts
    1,684
    Hi.

    For a one-off of 1000 records I would probably do something like:

    Code:
    DECLARE
      i NUMBER := 8;
    BEGIN
      FOR cur_rec IN (SELECT id_num
                      FROM   tab_prog
                      WHERE  progressive IS NULL
                      ORDER BY id_num)
      LOOP
        i := i + 1;
        UPDATE tab_prog
        SET    progressive = i
        WHERE  id_num = cur_rec.id_num;
      END LOOP;
      --COMMIT;
    END;
    /
    Note. I commented out the COMMIT, so you could check the results.

    Cheers

    Tim...
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: oracle-base.com
    My blog: oracle-base.com/blog

  3. #3
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    You can use analytic function to update the column.
    See the demo below:
    Code:
    SQL> select * from t1 ;
    
     OBJECT_ID OBJECT_NAME                    PROGRESSIVE
    ---------- ------------------------------ -----------
          2001 USER_VARRAYS                             1
          2002 USER_VARRAYS                             2
          2003 ALL_VARRAYS                              3
          2004 ALL_VARRAYS                              4
          2005 DBA_VARRAYS                              5
          2006 DBA_VARRAYS                              6
          2007 USER_OBJ_COLATTRS                        7
          2008 USER_OBJ_COLATTRS                        8
          2009 ALL_OBJ_COLATTRS                         9
          2010 ALL_OBJ_COLATTRS
          2011 DBA_OBJ_COLATTRS
          2012 DBA_OBJ_COLATTRS
          2013 USER_CONS_OBJ_COLUMNS
          2014 USER_CONS_OBJ_COLUMNS
          2015 ALL_CONS_OBJ_COLUMNS
          2016 ALL_CONS_OBJ_COLUMNS
          2017 DBA_CONS_OBJ_COLUMNS
          2018 DBA_CONS_OBJ_COLUMNS
          2019 ALL_SUMDELTA
          2020 ALL_SUMDELTA
    
    20 rows selected.
    
    SQL> get x2
      1  update t1 U
      2  set    u.progressive =
      3        ( select newval
      4           from
      5               (select object_id, object_name , maxval+rn newval
      6                 from ( select max(progressive) maxval
      7                         from t1
      8                         where progressive is not null
      9                      ) b,
     10                     ( select a.*, row_number() over (order by progressive) rn
     11                         from t1 a
     12                         where progressive is null
     13                     )
     14                ) O
     15            where O.object_id = u.object_id and
     16                  nvl(o.object_name,'x') = nvl(u.object_name ,'x')
     17         )
     18* where u.progressive is null
    SQL> /
    
    11 rows updated.
    
    SQL> select * from t1 ;
    
     OBJECT_ID OBJECT_NAME                    PROGRESSIVE
    ---------- ------------------------------ -----------
          2001 USER_VARRAYS                             1
          2002 USER_VARRAYS                             2
          2003 ALL_VARRAYS                              3
          2004 ALL_VARRAYS                              4
          2005 DBA_VARRAYS                              5
          2006 DBA_VARRAYS                              6
          2007 USER_OBJ_COLATTRS                        7
          2008 USER_OBJ_COLATTRS                        8
          2009 ALL_OBJ_COLATTRS                         9
          2010 ALL_OBJ_COLATTRS                        10
          2011 DBA_OBJ_COLATTRS                        11
          2012 DBA_OBJ_COLATTRS                        12
          2013 USER_CONS_OBJ_COLUMNS                   13
          2014 USER_CONS_OBJ_COLUMNS                   14
          2015 ALL_CONS_OBJ_COLUMNS                    15
          2016 ALL_CONS_OBJ_COLUMNS                    16
          2017 DBA_CONS_OBJ_COLUMNS                    17
          2018 DBA_CONS_OBJ_COLUMNS                    18
          2019 ALL_SUMDELTA                            19
          2020 ALL_SUMDELTA                            20
    
    20 rows selected.
    Tamil

Posting Permissions

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


Click Here to Expand Forum to Full Width