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
You can do it with a single query using CASE:
update emp set job=
when 'SALESMAN' then 'CLERK'
when 'CLERK' then 'SALESMAN'
where job in ('CLERK', 'SALESMAN')