Monday, April 11, 2011

Swapping Data Between 2 Columns (Part 2)

Create your own FREE Website


Alan Smale, a colleague of mine, suggested that Oracle takes data from a consistent point in time when it does updates. This allows you to update column 1 with column 2 and vice versa in the same statement. You can see what I mean in the example below:

SQL> create table swap2
  2  (first_name varchar2(10),
  3   surname    varchar2(10));
 
Table created.
 
SQL> insert into swap2 values ('Bloggs', 'Joe');
 
1 row created.
 
SQL> select * from swap2;
 
FIRST_NAME SURNAME
---------- ----------
Bloggs     Joe
 
SQL> update swap2 set
  2  first_name = surname,
  3  surname = first_name;
 
1 row updated.
 
SQL> select * from swap2;
 
FIRST_NAME SURNAME
---------- ----------
Joe        Bloggs
 
SQL>

No comments: