Monday, April 11, 2011

Swapping Data Between 2 Columns (Part 1)

Create your own  Social Website

I was not sure how to swap data between columns. It occurred to me that if I updated column 1 with column 2, I would then be unable to update column 2 with column 1. I decided you would have to do it using a temporary column as shown below. I will not provide any more explanation than that because once you have read part 2, you will not be using this method anyway:

SQL> create table swap1
  2  (first_name varchar2(10),
  3   surname    varchar2(10));
 
Table created.
 
SQL> insert into swap1 values ('Smith', 'John');
 
1 row created.
 
SQL> select * from swap1;
 
FIRST_NAME SURNAME
---------- ----------
Smith      John
 
SQL> alter table swap1 add
  2  (temporary_column varchar2(10));
 
Table altered.
 
SQL> update swap1 set temporary_column = first_name;
 
1 row updated.
 
SQL> update swap1 set first_name = surname;
 
1 row updated.
 
SQL> update swap1 set surname = temporary_column;
 
1 row updated.
 
SQL> alter table swap1 drop column temporary_column;
 
Table altered.
 
SQL> select * from swap1;
 
FIRST_NAME SURNAME
---------- ----------
John       Smith
 
SQL>

No comments: