Re: Updating date fields in tables?

From: James Petts <jpetts_at_celltech.co.uk>
Date: Thu, 29 Apr 1999 07:42:48 GMT
Message-ID: <37280c72.255486269@right.celltech>

On Wed, 28 Apr 1999 11:16:55 -0500, <yliu_at_creighton.edu> wrote:

>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;
>/

Just put in a counter variable, increment it each time you do an update, and insert a loop which commits, then resets the counter whenever you have updated a certain number of records.

Declare
num_updated integer :=0;

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;
        num_updated=num_updated+1;

        -- Inserted code below between IF... and END IF... lines
        IF num_updated = 5000 THEN
           commit;
           num_updated = 0;
        END IF;
      
        END LOOP

end;
>/
Received on Thu Apr 29 1999 - 02:42:48 CDT