help on update progressive!!!!

[raf]

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!

[TimHall]

Hi.

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

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...

[tamilselvan]

You can use analytic function to update the column.
See the demo below:

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