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

Submitted by admin on Wed, 2005-12-14 23:46
Body: 

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;

»

Comments

Submitted by Sreedhar Indla (not verified) on Wed, 2006-08-02 05:48.

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;

»

Submitted by sharvan (not verified) on Thu, 2006-09-14 04:54.

alter table emp drop (sal,deptno);

»

Submitted by Medo on Wed, 2012-12-12 06:27.

ALTER TABLE (TABLE NAME)
DROP(COL1,COL2,COL3);

COMMIT;

»