Re: Updating date fields in tables?

From: Kenny Gump <kgump_at_mylanlabs.com>
Date: Thu, 29 Apr 1999 07:51:03 -0400
Message-ID: <3728476f.0@news.mountain.net>

Declare
cursor date_cursor is select thrudate from my table where thrudate =

       to_date('31-dec-1999', 'dd-mon-yyyy')    for update of thrudate;
v_cnt number := 0;

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;
v_cnt := v_cnt + 1;
  if v_cnt >= 500 then

      commit;
      v_cnt := 0;

  end if;
END LOOP
end;
/

Try this code.

Kenny Gump

yliu_at_creighton.edu wrote in message ...
>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 Thu Apr 29 1999 - 06:51:03 CDT