OraFAQ Forum: SQL & PL/SQL » Add Column with default existing column

Home » SQL & PL/SQL » SQL & PL/SQL » Add Column with default existing column

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Add Column with default existing column [message #689857]

Fri, 24 May 2024 03:51

deepakdot
Messages: 93
Registered: July 2015

Member

Hi ,

I want to add a new Column to a table. the New Column data should be equal to another column.

Now this is what we do.

alter table T1 add NEXT_DATE DATE ;
Update T1 SET NEXT_DATE = LAST_DATE; --( LAST_DATE is an existing column)
commit;

But this table will have huge volume of rows. So Update will take a lot of time . Is there a way where we can alter with default value as existing column. something line

alter table T1 add NEXT_DATE DATE default LAST_DATE; -- Or something else which would be faster

Regards,
Deepak Samal

Report message to a moderator

Re: Add Column with default existing column [message #689858 is a reply to message #689857]

Fri, 24 May 2024 03:58

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

A trigger?

Report message to a moderator

Re: Add Column with default existing column [message #689859 is a reply to message #689858]

Fri, 24 May 2024 04:10

deepakdot
Messages: 93
Registered: July 2015

Member

The new data to the table for the new column , yes , a trigger can help .

Here I am worried for the existing rows for the new column. The tables data is really huge. How can I Add a column with a value from existing column.

Report message to a moderator

Re: Add Column with default existing column [message #689860 is a reply to message #689859]

Fri, 24 May 2024 04:13

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

You do it bit by bit or in parallel (see dbms_parallel_execute).

Report message to a moderator

Re: Add Column with default existing column [message #689861 is a reply to message #689857]

Fri, 24 May 2024 08:01

John Watson
Messages: 8976
Registered: January 2010
Location: Global Village

Senior Member

Quote:

I want to add a new Column to a table. the New Column data should be equal to another column.

Another solution:

Code: [Select all] [Show/ hide]

orclz>
orclz> select * from dept;

         DEPTNO DNAME          LOC
--------------- -------------- -------------
             10 ACCOUNTING     NEW YORK
             20 RESEARCH       DALLAS
             30 SALES          CHICAGO
             40 OPERATIONS     BOSTON

orclz> alter table dept add(newcol as (deptno*1));

Table altered.

orclz> select * from dept;

         DEPTNO DNAME          LOC                    NEWCOL
--------------- -------------- ------------- ---------------
             10 ACCOUNTING     NEW YORK                   10
             20 RESEARCH       DALLAS                     20
             30 SALES          CHICAGO                    30
             40 OPERATIONS     BOSTON                     40

orclz>

Report message to a moderator

Re: Add Column with default existing column [message #689862 is a reply to message #689860]

Fri, 24 May 2024 09:04

Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA

Senior Member

You could use calculated column:

Code: [Select all] [Show/ hide]

SQL> create table emp1 as select * from emp;

Table created.

SQL> desc emp1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                              NUMBER(4)
 ENAME                                     NOT NULL VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

SQL> alter table emp1 add ename2 varchar2(10) generated always as (ename || null) not null;

Table altered.

SQL> select ename,ename2 from emp1;

ENAME      ENAME2
---------- ----------
SMITH      SMITH
ALLEN      ALLEN
WARD       WARD
JONES      JONES
MARTIN     MARTIN
BLAKE      BLAKE
CLARK      CLARK
SCOTT      SCOTT
KING       KING
TURNER     TURNER
ADAMS      ADAMS

ENAME      ENAME2
---------- ----------
JAMES      JAMES
FORD       FORD
MILLER     MILLER

14 rows selected.

SQL>

SY.

[Updated on: Fri, 24 May 2024 09:05]

Report message to a moderator

Re: Add Column with default existing column [message #689863 is a reply to message #689862]

Fri, 24 May 2024 10:09

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

These were my first idea but the column does not always have this value, it is just a default value.
The column has this default value only for the current rows.

I bet if this column is added this is because it will have, at least from to time, a different value than the old and default one.

Report message to a moderator

Re: Add Column with default existing column [message #689865 is a reply to message #689863]

Fri, 24 May 2024 11:21

Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA

Senior Member

I see your point. OP must clarify if new column value must be always same as other column.

SY.

[Updated on: Fri, 24 May 2024 11:23]

Report message to a moderator

Re: Add Column with default existing column [message #689866 is a reply to message #689863]

Fri, 24 May 2024 12:14

John Watson
Messages: 8976
Registered: January 2010
Location: Global Village

Senior Member

Well, if it is only a default value that may be changed later, there is no problem at all. For several releases, Oracle does not actually update any rows when you add a column with a default value. It does it only when the row is selected.
@OP, do the test: you'll see that there is no significant redo and undo generated. It is just a DDL:

Code: [Select all] [Show/ hide]

orclz>
orclz> set timing on
orclz> alter table sales add (newcol varchar2(20) default 'instantaneous');

Table altered.

Elapsed: 00:00:00.09
orclz>

I don't kniw when this feature was introduced, but it has been around for a while.

Report message to a moderator

Re: Add Column with default existing column [message #689868 is a reply to message #689866]

Tue, 28 May 2024 04:49

cookiemonster
Messages: 13966
Registered: September 2008
Location: Rainy Manchester

Senior Member

OP wants the new column to default to the value of another column rather than a hard-coded value.
So you can't use the actual column default functionality. Instead you need to use triggers to set the default on new rows and an update to set it on the existing rows.

@deepakdot - define huge number of rows. There are only two ways to do this - update or CTAS (create table as select). It is possible to speed up with parallel processing if that option is available to you. You can also try diy parallel - split the rows into range chunks and have different sessions do each chunk. There's a package that does that and I can't remember what it's called.

Report message to a moderator

Re: Add Column with default existing column [message #689869 is a reply to message #689868]

Tue, 28 May 2024 04:50

cookiemonster
Messages: 13966
Registered: September 2008
Location: Rainy Manchester

Senior Member

The other possibility is to not set the default but instead change the code that reads the table to do nvl(next_date, last_date)

Report message to a moderator

Previous Topic:	More precision about log errors
Next Topic:	Left Outer Join Error (merged) (duplicate deleted by bb)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun May 04 06:31:12 CDT 2025