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;
---------- ----------
Bloggs     Joe
SQL> update swap2 set
  2  first_name = surname,
  3  surname = first_name;
1 row updated.
SQL> select * from swap2;
---------- ----------
Joe        Bloggs

No comments: