Series: Subject: Topic:
Question: 144 of 214

# 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;
Asked by: Interview Candidate | Asked on: Oct 10th, 2005
Showing Answers 1 - 4 of 4 Answers
kautilya

Answered On : Nov 7th, 2005

update set gender=

case where gender='F'  Then 'M'

where gender='M' Then 'F'

marutalksin

Answered On : 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'

Elena

Answered On : Nov 8th, 2007

update

set gender=(case gender when 'F' then 'M' when 'M' then 'F' end)

1 User has rated as useful.

Pallavi g rao

Answered On : Apr 22nd, 2011

View all answers by Pallavi g rao

update emp set gender=
case when gender='f' Then 'm'
else 'f'
end

If you think the above answer is not correct, Please select a reason and add your answer below.