Q

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

Learn how to use a CASE statement in SQL to convert all values in a column from female to male or male t female in this tip from SQL expert Karen Morton.

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.

This was first published in April 2010

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close