SQLTeam.com | Weblogs | Forums

Updating the table column Gender

tsql
sql2008r2

#1

I have a question on T-sql code. This is actually a interview question.
In a table, there is a column named Gender where values M and F are allowed.
The requirement is to update the column values, all the M should be updated as F and F should be updated as M.
Is it possible? How?
Please share your thoughts on this. Thank You


#2

What was your thought?

I'd just use a CASE statement in the UPDATE


#3

Thanks Kristen for the input.
I hope this will work..

update [Contacts] set Gender=(case
when Gender='M' then 'F'
when Gender='F' then 'M'
end)


#4

If any of the rows are neither "F" nor "M" that would muck them up.

I'd either want a report of them, up front, and get the users to fix that data (assuming that other values are not allowed) AND put a Constraint on that column to keep them in line.

Before that I would check if there was a constraint and if the column also allows NULLs

But other than that a where clause to only include rows that are either "F" or "M" would ensure the update was safe.