How to use a CASE statement in SQL to change gender values in a column

Suppose I have a column to determine whether the employee is male or female. The values are M or F. How do I convert all M to F and all F to M?

This one is really quite straightforward. You can simply use a CASE statement in the SET clause of your UPDATE statement to check to see what the GENDER value currently is and then set it to the opposing value. Here's an example using the familiar EMP table:

First, let's display the original data:

SQL> select * from emp;

          EMPNO ENAME      JOB       G             MGR HIREDATE              SAL            COMM          DEPTNO
--------------- ---------- --------- - --------------- --------- --------------- --------------- ---------------
           7369 SMITH      CLERK     M            7902 17-DEC-80             800                              20
           7499 ALLEN      SALESMAN  M            7698 20-FEB-81            1600             300              30
           7521 WARD       SALESMAN  M            7698 22-FEB-81            1250             500              30
           7566 JONES      MANAGER   M            7839 02-APR-81            2975                              20
           7654 MARTIN     SALESMAN  F            7698 28-SEP-81            1250            1400              30
           7698 BLAKE      MANAGER   M            7839 01-MAY-81            2850                              30
           7782 CLARK      MANAGER   M            7839 09-JUN-81            2450                              10
           7788 SCOTT      ANALYST   M            7566 19-APR-87            3000                              20
           7839 KING       PRESIDENT M                 17-NOV-81            5000
           7844 TURNER     SALESMAN  M            7698 08-SEP-81            1500               0              30
           7876 ADAMS      CLERK     F            7788 23-MAY-87            1100                              20
           7900 JAMES      CLERK     M            7698 03-DEC-81             950                              30
           7902 FORD       ANALYST   F            7566 03-DEC-81            3000                              20
           7934 MILLER     CLERK     M            7782 23-JAN-82            1300                              10

14 rows selected.

Now, let's execute the update to flip-flop the GENDER values:

SQL> update emp
  2  set gender = case when gender = 'F' then 'M' when gender = 'M' then 'F' end ;

14 rows updated.

Finally, let's verify the result:

SQL> select * from emp ;

          EMPNO ENAME      JOB       G             MGR HIREDATE              SAL            COMM          DEPTNO
--------------- ---------- --------- - --------------- --------- --------------- --------------- ---------------
           7369 SMITH      CLERK     F            7902 17-DEC-80             800                              20
           7499 ALLEN      SALESMAN  F            7698 20-FEB-81            1600             300              30
           7521 WARD       SALESMAN  F            7698 22-FEB-81            1250             500              30
           7566 JONES      MANAGER   F            7839 02-APR-81            2975                              20
           7654 MARTIN     SALESMAN  M            7698 28-SEP-81            1250            1400              30
           7698 BLAKE      MANAGER   F            7839 01-MAY-81            2850                              30
           7782 CLARK      MANAGER   F            7839 09-JUN-81            2450                              10
           7788 SCOTT      ANALYST   F            7566 19-APR-87            3000                              20
           7839 KING       PRESIDENT F                 17-NOV-81            5000
           7844 TURNER     SALESMAN  F            7698 08-SEP-81            1500               0              30
           7876 ADAMS      CLERK     M            7788 23-MAY-87            1100                              20
           7900 JAMES      CLERK     F            7698 03-DEC-81             950                              30
           7902 FORD       ANALYST   M            7566 03-DEC-81            3000                              20
           7934 MILLER     CLERK     F            7782 23-JAN-82            1300                              10

14 rows selected.