swapping values in a column oracle SQL plus

Database
N/A

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

Tags (2)
1 REPLY
Junior Contributor

Re: swapping values in a column oracle SQL plus

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')