Friday, December 17, 2010

Update set of tuples with new values : Oracle

declare

rCount integer;

j integer;

cursor cur_my_cursor is select * from my_table_name;

tRecordSet cur_my_cursor%Rowtype;

begin

rCount := 0; j:=1;

select count(id) into rCount from my_table_name;

dbms_output.put_line(rCount ' Records to be updated');

open cur_my_cursor; for j in 1..rCount

loop

fetch cur_my_cursor into tRecordSet;

update my_table_name mtn set mtn.unique_key = j

where mtn.field_one = tRecordSet.field_one

and mtn.field_two = tRecordSet.field_two

and mtn.field_three = tRecordSet.field_three

and mtn.field_four = tRecordSet.field_four

and mtn.field_five = tRecordSet.five;

dbms_output.put_line('Record id ' tRecordSet.field_one ' has been associated with an unique key of ' j );

end loop;

close cur_my_cursor; end;

No comments:

Post a Comment