CODEWITHSUNDEEP
oracle-databaseddl
ghoulfolk
ghoulfolk

Reputation: 336

Is there an easier way to swap column names with each other on the same table?

I need to find a way to swap 2 column names with each other in an Oracle table.

One way I could do this would be:

ALTER TABLE SCHEMANAME.TABLENAME
RENAME COLUMN FIRST TO X;

ALTER TABLE SCHEMANAME.TABLENAME
RENAME COLUMN SECOND TO Y;

ALTER TABLE SCHEMANAME.TABLENAME
RENAME COLUMN X TO SECOND;

ALTER TABLE SCHEMANAME.TABLENAME
RENAME COLUMN Y TO FIRST;

Is there an easier way to accomplish this?

Upvotes: 0

Views: 1465

Answers (1)

J. Chomel
J. Chomel

Reputation: 8393

For http://www.dba-oracle.com/t_change_column_order_within_oracle_table.htm, you cannot.

The order that the columns are stored on the data blocks can be changed but it's an academic exercise because it makes no difference whatsoever

It is the same a s for variables, but you cannot use the XOR swap technique ;)

That must be 3 statements: 

ALTER TABLE SCHEMANAME.TABLENAME RENAME COLUMN FIRST TO SWAPING_COL ;

ALTER TABLE SCHEMANAME.TABLENAME RENAME COLUMN SECOND TO FIRST ;

ALTER TABLE SCHEMANAME.TABLENAME RENAME COLUMN SWAPING_COL TO FIRST ;

Another solution is to create a new table with the order you like:

create table newtab
as   select SECOND, FIRST from SCHEMANAME.TABLENAME ;

  rename SCHEMANAME.TABLENAME  to TABLENAMEoldtab ; -- or drop it
  rename newtab to SCHEMANAME.TABLENAME ;

Upvotes: 1

Related Questions