URGENT - Comparing records in same cursor in pl-sq [message #37746] |
Tue, 26 February 2002 06:39 |
Paul Healy
Messages: 2 Registered: February 2002
|
Junior Member |
|
|
I have a program that requires that i extract records from a table. For each record retrieved i am then asked to loop through the rest of the corresponding extracted records to find if a record with data matching certain criteria exists.
If a match is found then i wish to extract certain values and and them to current cursor record. I then wish to delete the match as i no longer need it. After deleting i wish to save the new current reocrds data values.
The problem is that i cant work out how im meant to update the records in the table through cursor use when im needing to loop within a loop and also commit changes within both loops.
can anyone help, this is really desperate
Thanks
Paul
|
|
|
Re: URGENT - Comparing records in same cursor in pl-sq [message #37750 is a reply to message #37746] |
Tue, 26 February 2002 11:37 |
Jon
Messages: 483 Registered: May 2001
|
Senior Member |
|
|
Model your change on the following:
drop table table1;
create table table1 (col1 number, col2 number);
insert into table1 values (1,1);
insert into table1 values (1,2);
insert into table1 values (1,5);
insert into table1 values (2,3);
insert into table1 values (3,1);
insert into table1 values (3,2);
select * from table1;
declare cursor c1 is
select col1, col2, rowid
from table1 t1
where rowid > (select min(rowid) from table1 t2 where t1.col1 = t2.col1);
begin
for c1rec in c1
loop
update table1 set col2 = col2 + c1rec.col2
where rowid = (select min(rowid) from table1 where col1 = c1rec.col1);
delete from table1 where rowid = c1rec.rowid;
end loop;
end;
/
|
|
|
|
|
|