Interchange the values of two columns

You might face a situation where you need to interchange the values of 2 columns in an Oracle database table.  

E.g. there is a table employee having columns EMPID, FIRST_NAME, LAST_NAME and SALARY. By mistake the values of FIRST_NAME and LAST_NAME have been interchanged. Now you need to bring the data in correct state. 

  

You can think about following few options: 

  

Option-1 

1.    Alter table employee and add a new column TEMP_NAME to it. 

2. Update the values of LAST_NAME to TEMP_NAME. 
3. Update the LAST_NAME with the values of FIRST_NAME. 
4. Update the FIRST_NAME with the values of TEMP_NAME. 
5. Drop the column TEMP_NAME. 

Option-2 (For Oracle version 9i or higher) 

1.    Alter table employee and rename LAST_NAME column to TEMP_NAME. 

2. Alter table employee and rename column FIRST_NAME to LAST_NAME. 
3. Alter table employee and rename column TEMP_NAME to FIRST_NAME. 

Probably you can go ahead with any other solution as well. However there is one very simple solution.

Option-3 

Let do it by example: 

  

DROP TABLE EMPLOYEE; 

  

CREATE TBALE EMPLOYEE 

(

   EMPID             NUMBER 

  ,FIRST_NAME        VARCHAR2(30) 

  ,LAST_NAME         VARCHAR2(30) 

  ,SALARY            NUMBER 

); 

  

INSERT INTO EMPLOYEE VALUES (1,'Tendulkar','Sachin', 10000); 

INSERT INTO EMPLOYEE VALUES (1,'Ganguli','Saurabh', 10000); 

INSERT INTO EMPLOYEE VALUES (1,'Pathan','Irfan', 10000); 

INSERT INTO EMPLOYEE VALUES (1,'Khan','Jaheer', 10000); 

INSERT INTO EMPLOYEE VALUES (1,'Agarkar','Ajit', 10000); 

INSERT INTO EMPLOYEE VALUES (1,'Dravid','Rahul', 10000); 

SELECT * 

  FROM EMPLOYEE; 

UPDATE EMPLOYEE 

   SET FIRST_NAME = LAST_NAME 

      ,LAST_NAME  = FIRST_NAME; 

SELECT * 

  FROM EMPLOYEE; 

The update statement above solves the purpose. Is not it simple?  But how does it work?? 

For any DML (Insert, Update or Delete) oracle internally fires the row level triggers. You can read more about triggers at 

http://download-east.oracle.com/docs/cd/B10501_01/server.920/a96524/c18trigs.htm   and 

http://download-east.oracle.com/docs/cd/B10501_01/appdev.920/a96590/adg13trg.htm#431 

As you know in row level triggers the values of each column is stored in :OLD and :NEW parameters. For the above UPDATE statement oracle stores the old values of FIRAT_NAME and LAST_NAME in :OLD.FIRST_NAME and :OLD.LAST_NAME parameters respectively and then update  FIRST_NAME with :OLD.LAST_NAME  and  LAST_NAME with :OLD.FIRST_NAME.