Blog

Data Swapping of two rows

Posted 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
MCN is your source for developing solutions involving websites, mobile apps, cloud-computing, databases, BI, back-end services and processes and client-server applications.
SPONSORED BY
  • MCN is your source for developing solutions involving websites, mobile apps, cloud-computing, databases, BI, back-end services and processes and client-server applications.