Skip to Main Content
  • Questions
  • Can I change an existing column to a computed column with an Alter Table statement


Question and Answer

Connor McDonald

Thanks for the question, Aubreyh.

Asked: March 04, 2021 - 4:34 pm UTC

Last updated: March 05, 2021 - 5:03 am UTC

Version: 12c

Viewed 100+ times

You Asked

Test case can be found at:

Drop table t1;
Create table t1
    (c1 Int Default 0 Not Null,
     c2 Int Default 0 Not Null);

Is it then possible to:
Alter table t1
    Modify c2 as (c1 + 1);

When I do I get the following result, so I am beginning to believe this cannot be done:
Table dropped

Table created

Alter table t1
Modify c2 as (c1 + 1)

ORA-54026: Real column cannot have an expression

Appreciate your help as I have not been able to find any documentation that would confirm this behaviour.


with LiveSQL Test Case:

and we said...

Yes, but in a couple of steps

SQL> Create table t1
  2      (c1 Int Default 0 Not Null,
  3       c2 Int Default 0 Not Null);

Table created.

SQL> Alter table t1 set unused column c2;

Table altered.

SQL> alter table t1 add c2 int generated always as ( c1+1);

Table altered.

Here's a video about using set unused and not using drop column

More to Explore


Check out more PL/SQL tutorials on our LiveSQL tool.