SQL Query to Replace a Column Values from male to female and female to male

Topics covered:

• Create and view sample data
• Update using DECODE
• Update using CASE expression
• Update using simple CASE syntax
• ROLLBACK after testing

1. Create the sample table

Drop the table if it already exists:

DROP TABLE t_gender;

Create the table:

CREATE TABLE t_gender
(
  ename  VARCHAR(25),
  gender VARCHAR(6)
);

Insert sample data:

INSERT INTO t_gender VALUES ('SCOTT', 'MALE');
INSERT INTO t_gender VALUES ('KING', 'MALE');
INSERT INTO t_gender VALUES ('Jennifer', 'FEMALE');
INSERT INTO t_gender VALUES ('Shanta', 'FEMALE');
INSERT INTO t_gender VALUES ('Julia', 'FEMALE');

COMMIT;

View the table:

SELECT * FROM t_gender;

Sample output:

ENAME	GENDER
SCOTT	MALE
KING	MALE
Jennifer	FEMALE
Shanta	FEMALE
Julia	FEMALE

2. Update using DECODE

The DECODE function can be used to swap the values directly. Here, MALE becomes FEMALE and FEMALE becomes MALE.

UPDATE t_gender
SET gender = DECODE(gender, 'MALE', 'FEMALE', 'FEMALE', 'MALE');

Result:

ENAME	GENDER
SCOTT	FEMALE
KING	FEMALE
Jennifer	MALE
Shanta	MALE
Julia	MALE

Rollback:

ROLLBACK;

3. Update using CASE expression

The CASE expression is another easy way to swap values.

UPDATE t_gender
SET gender = (
  CASE
    WHEN gender = 'MALE' THEN 'FEMALE'
    ELSE 'MALE'
  END
);

Result:

ENAME	GENDER
SCOTT	FEMALE
KING	FEMALE
Jennifer	MALE
Shanta	MALE
Julia	MALE

Rollback:

ROLLBACK;

4. Update using simple CASE syntax

This version explicitly handles both values and keeps the original value unchanged in the ELSE clause.

UPDATE t_gender
SET gender = CASE gender
               WHEN 'MALE' THEN 'FEMALE'
               WHEN 'FEMALE' THEN 'MALE'
               ELSE gender
             END;

Result:

ENAME	GENDER
SCOTT	FEMALE
KING	FEMALE
Jennifer	MALE
Shanta	MALE
Julia	MALE

Rollback:

ROLLBACK;

Which method is best?

All three methods work well. CASE is usually preferred in modern Oracle SQL because it is clear, flexible, and easy to maintain.

Key Point:
DECODE is compact and useful for simple value replacement, while CASE is more readable and easier to extend.