Hope it'll help you...
Please let me know about results...
-----begin of script
declare
- Replace YOUR_TABLE with name of your table
- Decrease number of rows_to_commit if you have a small rollback segment
- but pay attention to it will increase time of execution.
rows_to_commit number:=10000000;
rows_processed number:=1;
cursor C1 is
select * from YOUR_TABLE order by code,create_date desc
for update;
prev_code YOUR_TABLE.code%type;
prev_created YOUR_TABLE.create_date%type;
begin
while rows_processed>0 loop
rows_processed:=0;
prev_code:=9.99e99;
for i in C1 loop
if i.code=prev_code then
if i.expire_date=prev_created then
null;
else
update YOUR_TABLE set expire_date=prev_created, status='N'
where current of C1;
rows_processed:=rows_processed+1;
end if;
else
prev_created:=to_date('2999','YYYY');
if i.expire_date is null and i.status='Y' then
null;
else
update YOUR_TABLE set expire_date=null, status='Y'
where current of C1;
rows_processed:=rows_processed+1;
end if;
prev_code:=i.code;
end if;
exit when rows_processed>=rows_to_commit;
prev_created:=i.create_date;
end loop
commit;
end loop;
end;
/
-------- end of script
"Dan White" <dwhite_at_icimail.com> wrote in message
news:9uj8qu029nj_at_drn.newsguy.com...
> Hello all,
>
> I have the following problem
> I have column the was incorectly populated(over time)
> what I need to do is updqte the expiration_date column to the correct
value
> here is my data(i have approxametly 8,000,000 rows to do this on )LN is
not an
> actual value in the table but only for illistration purposes
> what I need to do is update the expiration_date from LN1 to the
create_date of
> Ln 2
> and the expire date of ln2 with the create_date of ln3
> ANY HELP IS GREATLY APPRICATED AND THANKS IN ADVANCE
>
> what I have
> LN code create_date expire_date status
> ------- -------------------- --------------------- ------------
> 1 1002 07-SEP-2001 14:57:15 22-OCT-2001 07:42:26 N
> 2 1002 13-SEP-2001 01:01:35 22-OCT-2001 07:42:26 N
> 3 1002 22-OCT-2001 07:42:26 Y
>
> What I want to see
> LN code create_date expire_date status
> ------- -------------------- --------------------- ------------
> 1 1002 07-SEP-2001 14:57:15 13-SEP-2001 01:01:35 N
> 2 1002 13-SEP-2001 01:01:35 22-OCT-2001 07:42:26 N
> 3 1002 22-OCT-2001 07:42:26 Y
>
> Dan White
> programmer/analyst
>
Received on Tue Dec 04 2001 - 14:59:14 CST
