Home » SQL & PL/SQL » SQL & PL/SQL » Add Column with default existing column
Add Column with default existing column [message #689857] Fri, 24 May 2024 03:51 Go to next message
deepakdot
Messages: 91
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
Re: Add Column with default existing column [message #689858 is a reply to message #689857] Fri, 24 May 2024 03:58 Go to previous messageGo to next message
Michel Cadot
Messages: 68664
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

A trigger?

Re: Add Column with default existing column [message #689859 is a reply to message #689858] Fri, 24 May 2024 04:10 Go to previous messageGo to next message
deepakdot
Messages: 91
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.
Re: Add Column with default existing column [message #689860 is a reply to message #689859] Fri, 24 May 2024 04:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68664
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

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

Re: Add Column with default existing column [message #689861 is a reply to message #689857] Fri, 24 May 2024 08:01 Go to previous messageGo to next message
John Watson
Messages: 8938
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:
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>
Re: Add Column with default existing column [message #689862 is a reply to message #689860] Fri, 24 May 2024 09:04 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3275
Registered: January 2010
Location: Connecticut, USA
Senior Member
You could use calculated column:

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 Go to previous messageGo to next message
Michel Cadot
Messages: 68664
Registered: March 2007
Location: Nanterre, France, http://...
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.

Re: Add Column with default existing column [message #689865 is a reply to message #689863] Fri, 24 May 2024 11:21 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3275
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 Go to previous messageGo to next message
John Watson
Messages: 8938
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:
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.
Re: Add Column with default existing column [message #689868 is a reply to message #689866] Tue, 28 May 2024 04:49 Go to previous messageGo to next message
cookiemonster
Messages: 13925
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.
Re: Add Column with default existing column [message #689869 is a reply to message #689868] Tue, 28 May 2024 04:50 Go to previous message
cookiemonster
Messages: 13925
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)
Previous Topic: More precision about log errors
Next Topic: Left Outer Join Error (merged) (duplicate deleted by bb)
Goto Forum:
  


Current Time: Fri Jun 21 10:15:34 CDT 2024