conditional update on same column

Question

Hi

Pl find code below

SQL> desc aaa
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ENAME                                              VARCHAR2(1)

SQL> select * from aaa;

E
-
2
2
2
5
5
5
5

7 rows selected.  

I need to update 2 with 5 and 5 with 2 with a single sql.

Answer 1

According to the information provided and requirement:

In t-sql you can do this like

update aaa set Ename = case when Ename = '2' then '5' else '2' end

Change case statement with oracle equivalent

Answer 2

update
  aaa
set
  ENAME = case when ENAME = '2' then '5' else '2' end
where
  ENAME in ('2', '5')

Answer 3

UPDATE  aaa
SET E = 7 - E
WHERE E IN (5,2)

Answer 4

update aaa set ename = translate(ename, '25', '52')

or

update aaa set ename = decode(ename, '5', '2', '2', '5', ename)