All Forums Database
arwa 1 post Joined 05/15
12 May 2015
swapping values in a column oracle SQL plus

hi.
I want to know how to swap two values in a column in oracle sql table in a single query.
suppose I have a table emp which has a column job.
In job there are values 'CLERK' and 'SALESMAN' and others as well.
I want to swap 'SALESMAN' and 'CLERK'.
I did it with three queries.
update emp set job='salesman' where job='SALESMAN';
update emp set job='SALESMAN' where job='CLERK';
update emp set job='CLERK' where job='salesman';
I want to know how to do it in a single query or any other professional way to do it, as I am a student.
thanks in anticipation

dnoeth 4628 posts Joined 11/04
12 May 2015

You can do it with a single query using CASE:

update emp set job=
   case job
      when 'SALESMAN' then 'CLERK' 
      when 'CLERK' then 'SALESMAN' 
   END 
where job in ('CLERK', 'SALESMAN')

 

Dieter

You must sign in to leave a comment.