Re: Massive Update commit every 1000 records
- From: Alisher Yuldashev <yuldashev@xxxxxxxxxxx>
- To: wellmetus@xxxxxxxxx
- Date: Fri, 13 Nov 2009 15:17:26 -0500
Roger,
I would do
declare
cursor c12 is select rowid rid from sales where ssn is not null;
type v_typ is table of c12%rowtype;
v_arr v_typ;
begin
open c12;
loop
fetch c12 bulk collect into v_arr limit 1000;
forall i in 1..v_arr.count
update sales set ssn=to_number(substr(to_char(ssn),-4)) where
rowid=c12.rid(i);
commit;
exit when c12%notfound;
end loop;
close c12;
end;
Thanks,
--
Alisher Yuldashev
Senior Oracle DBA
The Pythian Group - Ottawa, Canada
Web : http://www.pythian.com
*Hi List,*
**
*Background:* 9i (9.2.0.8.0) HP-UX B.11.31 U ia64
* *
*We need to shrink SSN to the last 4 digit.*
* *
_update sales set ssn=to_number(substr(to_char(ssn),-4));_
*But the sales table is quite large and we want to commit every 1000
records.*
* *
*Does anyone have a better idea to do this rather than below?*
* *
*Thanks,*
* *
*Roger*
declare
cursor c12 is select ssn from sales for update of ssn;
n number := 0;
begin
for eachc12 in c12 loop
if eachc12.ssn is not null then
n := n + 1;
update sales set ssn=to_number(substr(to_char(ssn),-4)) where
current of c12;
if ( n = 1000 )
then
commit;
end if;
end if;
end loop;
commit;
end;
--
//www.freelists.org/webpage/oracle-l
Other related posts:
