Friday, January 22, 2021
More

    Swap Sex Field

    Must Read

    Programmerhttp://www.improgrammer.net
    We started this site to inspire young minds to motivate and encourage them towards Programming Language. In this site you will get programming tutorials, tech, programming facts, programming fun and programming blogs.

    Given a table salary, such as the one below, that has m=male and f=female values. Swap all f and m values (i.e., change all f values to m and vice versa) with a single update query and no intermediate temp table.

    For example:

    | id | name | sex | salary |
    |----|------|-----|--------|
    | 1  | A    | m   | 2500   |
    | 2  | B    | f   | 1500   |
    | 3  | C    | m   | 5500   |
    | 4  | D    | f   | 500    |
    

    After running your query, the above salary table should have the following rows:

    | id | name | sex | salary |
    |----|------|-----|--------|
    | 1  | A    | f   | 2500   |
    | 2  | B    | m   | 1500   |
    | 3  | C    | f   | 5500   |
    | 4  | D    | m   | 500    |
    

    Solution :
    Approach 1:
    update salary
      set sex =
      case
      when sex='m' then 'f'
      when sex='f' then 'm'
      else sex
      end

    Approach 2:

    update salary
    set sex = case sex
    when 'm' then 'f' else 'm'
    end

     

    Latest Articles

    More Recipes Like This