Q
Problem solve Get help with specific problems with your technologies, process and projects.

Swapping data of two rows without creating temp fields

Can I interchange the data of two rows of table without creating new temp fields? For example, we have a table...

dept (DeptName, DeptNo) with some data. I want to swap the all values of DeptName to DeptNo and DeptNo to DeptName.

Yes you can provide the DeptNAMe, DEPTNO are both VARCHAR2 or CHAR. But DEPTNO is of type NUMBER, you cannot SWAP using a single SQL statement.

Now, let us assume that the DeptNAME and DeptNo columns are of type VARCHAR2. However, DeptNO column is VARCHAR2(10) and DeptNAMe is VARCHAR2(100).

First of all DeptNo column needs to be modified to be 100.

i) ALTER TABLE DEPT MODIFY DEPTNO VARCHAR2(100);
Secondly, you will swap DEPTNAME and DEPTNO values using the SQL as follows:
ii)  update dept t1
      set deptname = (select deptno from dept2 t2 where t1.deptno = t2.deptno),
           deptno = (select deptname from dept2 t2 where t1.deptno =  t2.deptno);

Now, finally you want the deptname columns to be VARCHAR2(10)

iii) ALTER TABLE DEPT MODIFY DEPTNAME VARCHAR2(10).

If DEPTNO is a NUMBER column, you cannot alter the datatype unless the table is empty; in that case the whole swapping requirement would be moot.

Dig Deeper on Using Oracle PL-SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close