Updating date fields in tables?

From: <yliu_at_creighton.edu>
Date: Wed, 28 Apr 1999 11:16:55 -0500
Message-ID: <Pine.HPP.3.95.990428105744.29475A-100000@penguin.creighton.edu>

Hi Arjan, Patrick and all others,

Thank you so much for helping me to understand more about how to update a date field in tables. The two lines of code

e.g. 	update my_table 	
	set thrudate = to_char('31-dec-2099', 'DD-mon-yyyy') 
	where to_char(thrudate, 'dd-mon-yy') = '31-dec-99'; 

works great if the table size is not too big. When my table gets very big, it seems to take forever to update the thrudate field. I wrote pl/sql program to do the job, the following is the sketch of my pl/sql:

Declare

cursor	date_cursor is select thrudate from my table where thrudate =
		       to_date('31-dec-1999', 'dd-mon-yyyy')
   	for update of thrudate;

Begin
	FOR c1 in date_cursor LOOP
		update my_table
		set thrudate = to_date('31-dec-2099', 'dd-mon-yyyy') 
		where current of date_cursor;
	END LOOP

end;
/

The above pl/sql program works great. It speeds up the updating a lot. However, I want to break up the updating into several small subprocesses. For example, update 500 thrudates, commit and repeat the same process until all the thrudates got updated in one table. Could you please show me how to do this?

Thank you so much for all your help.

Best Regards,

Yongge Liu
yliu_at_creighton.edu Received on Wed Apr 28 1999 - 11:16:55 CDT