Search code examples
sqloracle-databasealter

using alter to add column with specific definition

how do i add a column to an existing table with a given definition. For example i have a table called employee with column called hire_date. Now using alter and only alter, i want to add a column called tenure which contains the difference between (hire_date and 14-JULY-2012). this is what ive tried

alter table employee add(tenure numeric(10) where tenure=(select to_date(
'14-JULY-2012')-HIRE_DATE from employee));
Solution

  • Starting from 11g, this should work:

    ALTER TABLE employee 
 ADD tenure GENERATED ALWAYS 
   AS (TO_DATE('20120714','YYYYMMDD') - hire_date) VIRTUAL