There is a Eno & gender in a table. Eno has primary key and gender has a check constraints for the values 'M' and 'F'. While inserting the data into the table M was misspelled as F and F as M. What is the update statement to replace F with M and M with F?

CREATE TABLE temp(
eno NUMBER CONSTRAINTS pk_eno PRIMARY KEY,
gender CHAR(1) CHECK (gender IN( 'M','F')));

INSERT INTO temp VALUES ('01','M');
INSERT INTO temp VALUES ('02','M');
INSERT INTO temp VALUES ('03','F');
INSERT INTO temp VALUES ('04','M');
INSERT INTO temp VALUES ('05','M');
INSERT INTO temp VALUES ('06','F');
INSERT INTO temp VALUES ('07','M');
INSERT INTO temp VALUES ('08','F');

COMMIT;

UPDATE temp SET gender =DECODE(gender,'M','F','F','M');

commit;

Interview Candidate
Oct 10th, 2005
8
4320

SQL

Answer

Showing Answers 1 - 8 of 8 Answers

kautilya

Nov 7th, 2005

update <TableName> set gender=
case where gender='F' Then 'M'
where gender='M' Then 'F'

marutalksin
Profile Answers by marutalksin Questions by marutalksin

Feb 27th, 2007

we can swap like

a=b;
b=c
c=a;

where a=f or F
b=m or M
c=temp
or

if gender = 'F'
then convert gender = 'm'
else if gender = 'M'
then convert gender = 'f'
// if we want only in caps
else if gender = 'f'
then convert gender = 'M'
else if gender = 'm'
then convert gender = 'F'