CODEWITHSUNDEEP
sqloracle-database
Pravin Satav
Pravin Satav

Reputation: 702

conditional update on same column

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.

Upvotes: 3

Views: 5406

Answers (4)

Kashif
Kashif

Reputation: 14440

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

Upvotes: 3

Michael Pakhantsov
Michael Pakhantsov

Reputation: 25390

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

Upvotes: 0

Kirill Leontev
Kirill Leontev

Reputation: 10941

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

or

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

Upvotes: 4

Constantin
Constantin

Reputation: 28204

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

Upvotes: 6

Related Questions