Re: update date filed in tables?

The sql looks good, but there are some small holes that could get you. The to_date ('31-dec-99', 'dd-mon-yy') syntax overrides the default conversion formatting. To_char(thrudate) = '31-dec-99' more precisely matches the default
behavior, but I'd probably run your version, anyway (your version requires only one date conversion, mine requires a conversion for each row).

Watch your rollback space. It sounds like you may be updating nearly every row on the table. You may want to pick an algorithm that breaks the updates into groups small enough to fit in your rollback area.

One last point. It appears the date '31-dec-99' is a flag of some sort. Are you SURE that you want to keep using this flag? Will your application(s) work correctly when a row has a "real" thrudate of '31-dec-99'?

Patrick Suppes

yliu_at_creighton.edu wrote:

> Hi all,
>
> I have about a dozen tables that have a 'thrudate' field. Right now, most
> of the values of the 'thrudate' field are '31-dec-99' (some are not). I
> need to update that value to '31-dec-2099'? Does the following SQL work?
> update <table1>
> set thrudate = to_date('31-dec-2099', 'dd-mon-yyyy')
> where thrudate = to_date ('31-dec-99', 'dd-mon-yy') ;
> All opinions are welcome.
>
> Thanks in advance.
>
> Yongge
> yliu_at_creighton.edu
Received on Fri Apr 23 1999 - 11:25:52 CDT