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

On 28 Apr 2003 07:22:12 -0700, kielhd_at_freenet.de (kielhd) wrote:

>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

If you want the value to change, then you are responsible for changing it yourself. You don't say how the table is defined, but I'd guess that LAST_UPDATE is defined with a default value of SYSDATE. That would explain you getting a value into LAST_UPDATE on the insert without you specifying it. But after that, it *has* *a* value so doesn't need a default value.

This is a perfect application for a trigger to insure that LAST_UPDATE gets updated whenever the row is updated, regardless of if the application remembers to or not. Received on Mon Apr 28 2003 - 10:54:27 CDT