-
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!
-
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...
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|