Interchange the Values of Columns

Sometimes you want to interchange the values of two columns in specific database table.

Suppose that I have EMP table with column EMPNO, ENAME, JOB, and by wrong the value in ENAME column  is the value of JOB column and vice versa.
To correct the data I should interchange the values of ENAME and JOB.

I have three solutions for doing this
1- Add temporary column to table
2- Rename columns
3-DML statement

Solution #1

Adding new column to interchange the values of two columns

1. Alter table EMP and add a new column TEMP_COL to it.
2. Update the TEMP_COL with the values of  ENAME.
3. Update the ENAME  with the values of JOB.
4. Update the JOB with the values of TEMP_NAME.
5. Drop the column TEMP_NAME.

 ALTER TABLE EMP  
  ADD (TEMP_COL VARCHAR2(100));  
 UPDATE EMP  
   SET TEMP_COL = ENAME;  
 UPDATE EMP  
   SET ENAME = JOB;  
 UPDATE EMP  
   SET JOB = TEMP_COL;  
 ALTER TABLE EMP DROP COLUMN TEMP_COL;  

Solution #2

Use rename column to replace each column name with the other column name

1-Rename ENAME column to TEMP_COL
2-Rename JOB column to ENAME
3-Rename TEMP_COL column to ENAME

 ALTER TABLE EMP  
 RENAME COLUMN ENAME TO TEMP_COL;  
 ALTER TABLE EMP  
 RENAME COLUMN JOB TO ENAME;  
 ALTER TABLE EMP  
 RENAME COLUMN TEMP_COL TO ENAME;  

Solution #3

Use DML statement to do this this issue and this is the easiest solution as it doesn't change structure of table and don't submit any DDL operation.

 UPDATE EMP  
   SET ENAME = JOB, JOB = ENAME;  

Thanks