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.
Re: Convert Gender Column
The query can be done but there is no way to write a single query for your requriment
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)
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);
Re: Convert Gender Column
[QUOTE=jamesravid;19145]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);[/QUOTE]
ok Thanks ........................
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')
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)
Re: Convert Gender Column
[QUOTE=vvk.kotamraju;18776]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.[/QUOTE]
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.