0

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?

William Robertson
  • 15,273
  • 4
  • 38
  • 44
ghoulfolk
  • 326
  • 7
  • 17
  • 3
    You don't need both X and Y. One is sufficient. First To X, Second to First, X To Second would do. I hope you do not mean changing the order of columns. Another way would be doing a select into another table renaming the columns. – Cetin Basoz Sep 20 '18 at 13:12
  • oh yeah thats true. I can skip one alter with that but was wondering if there is some one liner that could pull that off on plsql – ghoulfolk Sep 20 '18 at 13:15
  • 1
    As Cetin said - swapping two variables only requires one extra variable, not two (and only three statements, not four). With that said, even your solution, slightly less efficient as it is, is exceptionally simple and efficient. ("Easy") Why do you need anything easier than that? –  Sep 20 '18 at 13:16
  • If there is a syntax to do it with less sql, it would be great for cases where multiple tables need to be modified by swopping column names within them, so that I wouldn't have to write 3 alters per table – ghoulfolk Sep 20 '18 at 13:32
  • Alas, Oracle SQL does not support multiple column name changes in a single ALTER TABLE statement. Rather, you could write a small PL/SQL procedure that takes a table name and two column names as inputs (and perhaps a schema name as well), which would swap the two column names; then you can call the procedure repeatedly, as needed. –  Sep 20 '18 at 13:54
  • By the way, `ALTER TABLE` is SQL not PL/SQL. PL/SQL is the programming language. – William Robertson Sep 20 '18 at 14:18
  • By one way to create a new table as column name expected "CREATE TABLE SCHEMANAME.NEW TABLENAME AS SELECT COLUMN FIRST AS X , COLUMN SECOND AS Y , COLUMN X AS SECOND , COLUMN Y AS FIRST FROM SCHEMANAME.TABLENAME;" – kanagaraj Sep 20 '18 at 15:51

1 Answers1

1

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 ;
J. Chomel
  • 8,193
  • 15
  • 41
  • 69
  • This makes it sound like its all about the order of the columns, but it is not. Swapping the names is not the same as swapping the order of the columns. Swapping the names will also swap the data type, constraints, and data. – Matthew McPeak Sep 20 '18 at 17:00