In this blog, I have discussed how to interchange data of two rows of table with out creating temp fields.

For instance, Let us create a table with columns EMPNO,ENAME and JOB.

CREATE TABLE EMP
(
EMPNO     NUMBER(4) NOT NULL PRIMARY KEY,
ENAME     VARCHAR2(10),
JOB       VARCHAR2(10));

First of all we can insert some Sample Insert Statements :

Insert into EMP
(EMPNO, ENAME, JOB)
Values
(8957, 'LUTHER', 'ENGINEER');
Insert into EMP
(EMPNO, ENAME, JOB)
Values
(8020, 'JAMES', 'CLERK');
Insert into EMP
(EMPNO, ENAME, JOB)
Values
(8955, 'BLAKE', 'MANAGER');

Secondly, we swap all the values from ENAME to JOB and JOB to ENAME.

Query:

UPDATE EMP A
SET ENAME= (SELECT JOB
                  FROM EMP b
WHERE A.EMPNO= b.EMPNO),
       job = (SELECT ENAME
FROM EMP b
WHERE A.EMPNO= b.EMPNO);

And as a result of the query the data of rows ENAME and JOB is swapped.

If the columns are of different datatypes, for example, Swapping of data between EMPNO and ENAME does not meet as they are of varying datatypes.

Conclusion:

In this blog, I have discussed how to swap data of two rows with same datatypes without creating temporary fields.