Update/Change Male to Female and Female to Male in MySQL

This question is commonly asked in SQL and MySQL interviews. In this tutorial, we will show you how to change, update or replace gender values from male to female or female to male using a single query in MySQL or SQL.

How to Update/Change Gender Male to Female and Female to Male in MySQL

Here are a few approaches to change, update or replace gender values from male to female or female to male using a single query in MySQL or SQL:

• Approach 1: Using UPDATE Statement with CASE
• Approach 2: Using IF Function
• Approach 3: Using ENUM Type

Approach 1: Using UPDATE Statement with CASE

The UPDATE statement with a CASE expression allows for conditional updates in a single query. Here’s an example mysql query of how you can use it to change or update male to female and female to male:

-- Change male to female
UPDATE your_table
SET gender = CASE
    WHEN gender = 'male' THEN 'female'
    ELSE gender
END;

-- Change female to male
UPDATE your_table
SET gender = CASE
    WHEN gender = 'female' THEN 'male'
    ELSE gender
END;

Approach 2: Using IF Function

The IF function simplifies conditional updates. This method provides a concise way to change gender values based on the existing values. Here’s an example of how you can use it to change or update male to female and female to male:

-- Change male to female
UPDATE your_table
SET gender = IF(gender = 'male', 'female', gender);

-- Change female to male
UPDATE your_table
SET gender = IF(gender = 'female', 'male', gender);

Approach 3: Using ENUM Type

If your gender column is defined as an ENUM type, you can use direct updates based on the existing values.

Here’s an example of how you can use it to change or update male to female and female to male:

-- Change male to female
UPDATE your_table
SET gender = 'female'
WHERE gender = 'male';

-- Change female to male
UPDATE your_table
SET gender = 'male'
WHERE gender = 'female';

Conclusion

In this tutorial, you have learned multiple methods to update gender male to female and female to male values in a MySQL database.

Recommended Tutorials