Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Nov 2014
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question SQL statement to update values?

    Hello,
    I want to update all M (Male) and F (Female) values (change all F values to M and vice versa) as per following table.

    Id >> Name >> Gender >> Salary
    1 >> A >> M >> 10000
    2 >> B >> F >> 5000
    3 >> C >> M >> 8500
    4 >> D >> F >> 1500

    How should I write the SQL statement to update the values?

  2. #2
    4 Star Lounger
    Join Date
    Jan 2010
    Location
    Fort McMurray, Alberta, Canada
    Posts
    559
    Thanks
    51
    Thanked 68 Times in 66 Posts
    First of all, make a backup that is 100% certain. You need something to go back to should anything go wrong. Do not skip this step FOR ANY REASON. If you have doubts then make 2 backups.

    Second, I've never figured out how to perform variable updates in one step. So this is going to take 4 steps. All the following assumes that the column named Gender is of type Char(1). Here goes!

    -- Step 1.
    -- We need a safe Set aside value. Therefore the result of this query MUST be empty.
    -- If this query does not return an empty set, stop right here!
    Select *
    From TableName
    Where Gender = "!"

    -- Step 2.
    -- Set aside all the Males.
    -- It's very important that our set-aside value ("!"), not be currently used anywhere.
    Update TableName
    Set Gender = "!"
    Where Gender = "M"

    -- Step 3.
    -- Convert all the Females to Males.
    Update TableName
    Set Gender = "M"
    Where Gender = "F"

    -- Step 4.
    -- Convert all the Males to Females.
    -- We can only do this accurately because we set them aside in Step 1.
    Update TableName
    Set Gender = "F"
    Where Gender = "!"


    And that's done. Note that this logic, by design, does not change Null (undefined) genders, intersex genders, or anything else. It confines itself to reversing Males to Females, and vice versa.

  3. #3
    New Lounger
    Join Date
    Apr 2015
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi,
    Try the following SQL statement.

    Code:
    UPDATE TableName SET Gender = CASE Gender WHEN 'M' THEN 'F' ELSE 'M' END

  4. #4
    New Lounger
    Join Date
    Nov 2014
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Thumbs up

    Thank you guys for your reply.
    I'll try the both option and will update you shortly.
    Last edited by DennyTrend; 2015-04-02 at 08:29. Reason: Typo

  5. #5
    4 Star Lounger
    Join Date
    Jan 2010
    Location
    Fort McMurray, Alberta, Canada
    Posts
    559
    Thanks
    51
    Thanked 68 Times in 66 Posts
    Quote Originally Posted by KelvinSmith View Post
    Hi,
    Try the following SQL statement.

    Code:
    UPDATE TableName SET Gender = CASE Gender WHEN 'M' THEN 'F' ELSE 'M' END
    Thumbs up on this response. It is more efficient and more readable than my solution. I tend to be very conservative with code because there are a lot of extensions to ANSI SQL and the OP's runtime environment was not specified. But to be totally honest, I have never memorized both the ANSI standard and the variances, to be able to distinguish them. And even more honest, I briefly considered a single statement version but had a failure of imagination in how to implement that.

    There's one improvement to the suggested code I'd make:

    Code:
    UPDATE TableName SET Gender = CASE Gender WHEN 'M' THEN 'F' WHEN 'F' THEN 'M' END
    This avoids assigning Null and intersex genders to Male.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •