Re: How to update a column to its default value?

> Hi,
> I am looking into this problem for a while without solving it.
> HELP!!!!!!!!!!
>
> The following table is defined:
>
> CREATE TABLE DEFAULT_TABLE(
> TEXT varchar2(32),
> LAST_UPDATE date default sysdate
> );
>
> After setting an initial value to the column TEXT with the command
>
> insert into DEFAULT_TABLE ( TEXT ) values ( 'John' )
>
> the table looks like:
>
> select text, to_char(last_update, 'dd-mon-yyyy hh24:mi:ss') from
> DEFAULT_TABLE
>
> TEXT TO_CHAR(LAST_UPDATE,
> -------------------------------- --------------------
> John 28-apr-2003 15:53:01
>
>
> Here is my Problem:
> -------------------
> I need to update the row, where the column TEXT equals the value
> 'John' to the value 'George'. I expect the value in the column
> LAST_UPDATE to change automatically to the current value of SYSDATE.
>
> Here is what I did:
>
> update DEFAULT_TABLE set TEXT = 'George' where TEXT = 'John';
>
> Afterwards, the table looks like this:
>
> select text, to_char(last_update, 'dd-mon-yyyy hh24:mi:ss') from
> DEFAULT_TABLE;
>
> TEXT TO_CHAR(LAST_UPDATE,
> -------------------------------- --------------------
> George 28-apr-2003 15:53:01
>
> The value of the column TEXT has changed as expected, but the value of
> LAST_UPDATE remained the same.
> I want the value of the column LAST_UPDATE to change when I update the
> column TEXT.
>
> Hope there is some help to this problem!
>
> Regards from rainy Germany
> Henning

On Oracle 9i, you can do it like this:    

   SQL> CREATE TABLE DEFAULT_TABLE(

     2  TEXT            varchar2(32),
     3  LAST_UPDATE     date default sysdate
     4  );
   

   Table created.    

   SQL> insert into DEFAULT_TABLE ( TEXT ) values ( 'John' );    

   1 row created.    

   SQL> select text, to_char(last_update, 'dd-mon-yyyy hh24:mi:ss') from      2 DEFAULT_TABLE;    

   TEXT                             TO_CHAR(LAST_UPDATE,
   -------------------------------- --------------------
   John                             28-apr-2003 19:34:06
   

time passes...

   SQL> update DEFAULT_TABLE set TEXT = 'George', last_update=default      2 where TEXT = 'John';    

   1 row updated.    

   SQL> select text, to_char(last_update, 'dd-mon-yyyy hh24:mi:ss') from      2 DEFAULT_TABLE;    

   TEXT                             TO_CHAR(LAST_UPDATE,
   -------------------------------- --------------------
   George                           28-apr-2003 19:35:19

hth

Rene Nyffenegger

-- 
  Projektleitung und Entwicklung in Oracle/C++/C# Projekten
  http://www.adp-gmbh.ch/cv.html