Blog

Data Swapping of two rows

By Shankar M Blogs | SQL Dec 14, 2012
Interchanging data of two rows of table with out temporary fields.
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.
COMMENT USING

PREMIUM SPONSORS

Hire Mobile & Web Developer on demand. 100% satisfaction. Try for 1 week or Money Back. Local and remote developers available all over USA.

Latest Blogs