Results 1 to 9 of 9

Thread: Convert Gender Column

  1. #1
    Junior Member
    Join Date
    Oct 2005
    Answers
    1

    Convert Gender Column

    Hi...

    I have a Question in Oracle.. Please give me the SQL Statement for the following condition. I have a table with columns name, gender. In that table i have 20 in which 10 rows are having Gender as "Male" and the rest of the rows are having a gender as "Female". Now i need to convert the Gender column as Males to Female & Females to Male. Is it posible to make this in a single query. please give me.


  2. #2
    Moderator
    Join Date
    Jun 2007
    Answers
    2,074

    Re: Convert Gender Column

    You need to write three update statments for the same.

    1.Update all Male to Null.
    2.Update all Female to Male.
    3.Update all Null to Female.


  3. #3
    Junior Member
    Join Date
    Jul 2007
    Answers
    6

    Re: Convert Gender Column

    The query can be done but there is no way to write a single query for your requriment


  4. #4
    Expert Member
    Join Date
    Apr 2007
    Answers
    500

    Re: Convert Gender Column

    This Query may be use full to u .Try this .....
    update emp_check a SET (gender) =(SELECT DECODE(gender,'F','M','M','F') FROM emp_check WHERE a.empno=empno)


  5. #5
    Expert Member
    Join Date
    Jun 2006
    Answers
    410

    Re: Convert Gender Column

    No need for a correlated query. The following query does the same,

    update emp_check a SET gender = decode (gender,'M','F','F','M',gender);


  6. #6
    Expert Member
    Join Date
    Apr 2007
    Answers
    500

    Re: Convert Gender Column

    Quote Originally Posted by jamesravid View Post
    No need for a correlated query. The following query does the same,

    update emp_check a SET gender = decode (gender,'M','F','F','M',gender);
    ok Thanks ........................


  7. #7
    Junior Member
    Join Date
    Sep 2007
    Answers
    2

    Thumbs up Re: Convert Gender Column

    Please try this create table a ( name char (1), sex varchar2 (15)) update a set sex = decode ( sex ,'m','f','f','m')


  8. #8

    Re: Convert Gender Column

    You can use this query to update the gender code from male to female and viceversa.

    update tablename SET (gender) = (SELECT DECODE(gender,'F','M','M','F') FROM tablename)


  9. #9
    Junior Member
    Join Date
    Nov 2010
    Answers
    1

    Re: Convert Gender Column

    Quote Originally Posted by vvk.kotamraju View Post
    Hi...

    I have a Question in Oracle.. Please give me the SQL Statement for the following condition. I have a table with columns name, gender. In that table i have 20 in which 10 rows are having Gender as "Male" and the rest of the rows are having a gender as "Female". Now i need to convert the Gender column as Males to Female & Females to Male. Is it posible to make this in a single query. please give me.
    we can do it in the following way
    Imagine you have a table name gender that has name and gender column.
    create a table gendernew like create table gendernew as
    select * from(select name, R||S as gen
    from(

    select name,case p
    when 'M' then 'F'
    end R,
    case Q
    when 'F' then 'M'
    end S from(
    select name, replace(gender,'F', '') as p , case gender
    when 'F' then 'F'
    end Q
    from gender))
    once new table is created, you can update the old table as following
    update gender G
    set gender = (
    select gen
    from gennew GE
    where G.name = GE.name).
    if you want you can drop the table as
    drop table gennew.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
About us
Applying for a job can be a stressful and frustrating experience, especially for someone who has never done it before. Considering that you are competing for the position with a at least a dozen other applicants, it is imperative that you thoroughly prepare for the job interview, in order to stand a good chance of getting hired. That's where GeekInterview can help.
Interact