In this article, we will Implement a Query to Replace Column Values from ‘male’ to ‘female’ and ‘female’ to ‘male’. For a better explanation, we will Implement this Query with an Example. For Implementation of this Query first of all we will create a database. Name of Database “Sample”.
After that Inside the Database, We will Create a Table. The name of the table is “EMPDATA”. Here we will replace column values from ‘Male’ to ‘female’ and ‘female’ to ‘male’ with the help of UPDATE, CASE statement, and condition.
Now, we have to follow the below statement for Implementing this Query.
Step 1: Create a database
For database creation, there is the query we will use in MS SQL Server.
Query:
CREATE DATABASE Sample;
Step 2: Use a database
for using the database
Query:
use database_name; for this database… use Sample;
Step 3: Create a table
For the creation data table, we will use this below query
Query:
CREATE TABLE EMPDATA ( EMPNAME VARCHAR(25), GENDER VARCHAR(6), DEPT VARCHAR(20), CONTACTNO BIGINT NOT NULL, CITY VARCHAR(15) );
Step 4: Structure of the table
In SQL with the Help of the EXEX sp_help table name, we can see the structure of the table. Like a number of columns, data type, size, nullability, and constraints. EXEC Sp_ help Query is similar to DESC or DESCRIBE Query.
Query:
EXEC sp_help EMPDATA
Output:
Step 5: Insert a value into a table
Query:
INSERT INTO EMPDATA
VALUES ('VISHAL','MALE','SALES',9193458625,'GHAZIABAD'),
('DIVYA','FEMALE','MANAGER',7352158944,'BAREILLY'),
('REKHA','FEMALE','IT',7830246946,'KOLKATA'),
('RAHUL','MALE','MARKETING',9635688441,'MEERUT'),
('SANJAY','MALE','SALES',9149335694,'MORADABAD'),
('ROHAN','MALE','MANAGER',7352158944,'BENGALURU'),
('RAJSHREE','FEMALE','SALES',9193458625,'VODODARA'),
('AMAN','MALE','IT',78359941265,'RAMPUR'),
('RAKESH','MALE','MARKETING',9645956441,'BOKARO'),
('MOHINI','FEMALE','SALES',9147844694,'Delhi')
SELECT * FROM EMPDATA;Output:
Step 6: Implementation of the query to replace column values from ‘Male’ to ‘Female’ and ‘Female’ to ‘Male’.
Finally, in this step, We will Implement the query to replace column values from ‘male’ to ‘female’ and ‘female’ to ‘male’.Here We are using UPDATE, and CASE statements.
We can use an Update statement with WHERE Clause or Without WHERE CLAUSE.
- Update statement without where clause: The Update Statement Without the Where Clause is Used to Update all rows in a table.
Query:
UPDATE [EMPDATA] SET GENDER = ‘FEMALE’;
This Query will Update GENDER = ‘FEMALE’ for all rows.
- Update statement with where clause: The Update Statement with the Where clause is used to Update a single or multiple rows on the basis of the WHERE clause in SQL Server.
Query:
UPDATE [EMPDATA] SET GENDER = ‘FEMALE’ WHERE EMPNAME = ‘AMAN’
- For Multiple Column Updating:
Query:
UPDATE [EMPDATA] SET GENDER = ‘FEMALE’ WHERE EMPNAME = ‘AMAN’ or EMPNAME = ‘VIJAY’;
Now we will IMPLEMENTATION OF Query to Replace a Column Values from ‘male’ to ‘female’ and ‘female’ to ‘male’
Query:
UPDATE [EMPDATA] SET GENDER = (CASE WHEN GENDER ='MALE' THEN 'FEMALE' WHEN GENDER = 'FEMALE' THEN 'MALE' END )
Execute the following code and notice that we want to evaluate CASE Gender in this query.
Output: