Hi,
I am currently running on 12c and was seeing if the below was possible as I seem to be getting an eror when I try.
I am trying to add a column using a select statement based on another table. As an example on the 3 tables below. The USER_NAME is likely to be ammended for alot of users.
SQL> desc t1
Name Null? Type
----------------------------------------- -------- ----------------------------
USER_ID VARCHAR2(10)
USER_NAME VARCHAR2(20)
SQL> desc t2
Name Null? Type
----------------------------------------- -------- ----------------------------
USER_NAME VARCHAR2(20)
FORENAME VARCHAR2(20)
SURNAME VARCHAR2(20)
SQL> desc t3
Name Null? Type
----------------------------------------- -------- ----------------------------
USER_NAME VARCHAR2(20)
ADDRESS1 VARCHAR2(50)
ADDRESS2 VARCHAR2(50)
ADDRESS3 VARCHAR2(50)
Instead of changing each table I was looking to add the USER_NAME as a virtual column based on t1. I have tried the below but I get an error:
SQL> alter table t3 add (USER_NAME_TEST as (select USER_NAME from t1);
alter table t3 add (USER_NAME_TEST as (select USER_NAME from t1)
*
ERROR at line 1:
ORA-00936: missing expression
Is this type of column allowed or would I need to look at creating a trigger that updates the other tables once a change has been made?
Thanks