Interchange the Values of 2 Columns

You might face a situation where you need to interchange the values of two columns in an Oracle database table. This article will explore ways to achieve this.

Let's assume you have a employee table with columns EMPID, FIRST_NAME, LAST_NAME and SALARY. By mistake, the values of FIRST_NAME and LAST_NAME have been interchanged. You now need to bring the data in correct stage.

Possible solutions to the problem:

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

The simplest solution. 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','Irphan',10000);
INSERT INTO EMPLOYEE VALUES(1,'Khan','Jaheer',10000);
INSERT INTO EMPLOYEE VALUES(1,'Agarkar','Ajit',10000);
INSERT INTO EMPLOYEE VALUES(1,'Darvid','Rahul',10000);

SELECT *
  FROM EMPLOYEE;
  
UPDATE EMPLOYEE
   SET FIRST_NAME = LAST_NAME
      ,LAST_NAME  = FIRST_NAME;

SELECT *
  FROM EMPLOYEE;

You can see the update statement above. Isn't 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 the :OLD parameter and then update FIRST_NAME with :OLD.LAST_NAME and LAST_NAME with :OLD.FIRST_NAME.

Hope it helps!