Updating the values that are wrongly entered using T-SQL

One day one of my friend has aked me that she has observed that she has wrongly entered the gender in a table in a database i.e.,

–          For Female insetead of entering F(Female) entered as M(Male)

–          For Male insetead of entering M(Male) entered as F(Female)

We can correct this instance by having a single update command. Below is an example that illustrates to correct the above scenario.

Let’s say, we have a table in a database named as Test which has a column named as Gender. Below is the creation of statements:

CREATE TABLE TEST(GENDER VARCHAR(1))

INSERT INTO TEST VALUES('F')
INSERT INTO TEST VALUES('M')
INSERT INTO TEST VALUES('F')
INSERT INTO TEST VALUES('M')
INSERT INTO TEST VALUES('F')
INSERT INTO TEST VALUES('M')
INSERT INTO TEST VALUES('F')
INSERT INTO TEST VALUES('M')

SELECT * FROM TEST

UPDATE TEST SET GENDER = B.GENDER
FROM
TEST A
JOIN
(
        SELECT 
             DISTINCT CASE GENDER  WHEN 'F' THEN 'M' ELSE 'F' END GENDER 
        FROM  TEST
) B
ON A.GENDER <> B.GENDER

OR

UPDATE TEST SET GENDER = (SELECT DISTINCT CASE A.GENDER  WHEN 'F' THEN 'M' 
                          ELSE 'F' END FROM  TEST A 
                          WHERE A.GENDER = TEST.GENDER
                         )

SELECT * FROM TEST

Leave a comment

2 Comments

  1. Couldn’t have said it better myself.

  2. Love your blog!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: