How does one drop/ rename a columns in a table?


Drop a column

From Oracle8i one can DROP a column from a table. Look at this sample script, demonstrating the ALTER TABLE table_name DROP COLUMN column_name; command.

Workarounds for older releases:

SQL> update t1 set column_to_drop = NULL;
SQL> rename t1 to t1_base;
SQL> create view t1 as select >specific columns> from t1_base;

SQL> create table t2 as select >specific columns> from t1;
SQL> drop table t1;
SQL> rename t2 to t1;

Rename a column

From Oracle9i one can RENAME a column from a table. Look at this example:

ALTER TABLE tablename RENAME COLUMN oldcolumn TO newcolumn;

Workarounds for older releases:

Use a view with correct column names:

rename t1 to t1_base;
create view t1 >column list with new name> as select * from t1_base;

Recreate the table with correct column names:

create table t2 >column list with new name> as select * from t1;
drop table t1;
rename t2 to t1;

Add a column with a new name and drop an old column:

alter table t1 add ( newcolame datatype );  
update t1 set newcolname=oldcolname;
alter table t1 drop column oldcolname;


For dropping a column from a table,

for example,if we want to drop last_name column from employees

ALTER TABLE employees
DROP COLUMN last_name;

For renaming a column,

for example,if we want to rename last_name column to middle_name in employees table

ALTER TABLE employees
RENAME COLUMN last_name TO middle_name;

alter table emp drop (sal,deptno);