CODEWITHSUNDEEP
oracle-database
Apurv
Apurv

Reputation: 3753

Not update existing records with default value of new column

I have a table with some records.

I want to add a TIMESTAMP type of column (LAST_MODIFIED) to this table. I want to set the DEFAULT value of this new column to SYSDATE.

But I want to make sure that when this column is added, existing records does not get this column value as SYSDATE. How to achieve this ?

Upvotes: 0

Views: 2291

Answers (1)

Sanders the Softwarer
Sanders the Softwarer

Reputation: 2496

You should do it as two separate actions: adding a column and setting default value.

SQL> create table some_data (id integer);

Table created

SQL> insert into some_data select rownum from dual connect by level <= 5;

5 rows inserted

SQL> alter table some_data add date_modified date;

Table altered

SQL> alter table some_data modify date_modified default sysdate;

Table altered

SQL> insert into some_data (id) values (6);

1 row inserted

SQL> select * from some_data;

                                     ID DATE_MODIFIED
--------------------------------------- -------------
                                      1 
                                      2 
                                      3 
                                      4 
                                      5 
                                      6 17.03.2015

6 rows selected

Upvotes: 3

Related Questions