Massive Update commit every 1000 records
- From: Roger Xu <wellmetus@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 13 Nov 2009 13:50:05 -0600
*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;
Other related posts:
