Question: 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?
Answer: 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;
|
