You can use analytic function to update the column.
See the demo below:
TamilCode: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.




Reply With Quote