Printable View
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.
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.
The query can be done but there is no way to write a single query for your requriment
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)
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=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 ........................
Please try this create table a ( name char (1), sex varchar2 (15)) update a set sex = decode ( sex ,'m','f','f','m')
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)
[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.