Update query - To swap values in a column with check constraint
466336Dec 15 2005 — edited Dec 15 2005Hi all,
I need help with an update query.
I have a table with two columns(only 2 columns and no primary keys) - name and gender. Lets say I have 4 rows in that table.
Name Gender
------------------------------------
Lucy M
John F
Linda M
Tom F
Now that I realised that values were incorrectly entered into the table. "M" was entered for female and "F" was entered for male. Now I have to swap these values through a single update statement and without disabling the "CHECK" constraint.
Your help is highly appreciated.
Regards,
Aravind.
Table creation scripts
------------------------------
CREATE TABLE test
(
Name VARCHAR2(10),
GENDER CHAR(1),
CONSTRAINT gen_chk CHECK (GENDER IN ('M','F'))
);
INSERT INTO
test (name, gender)
VALUES ('Lucy','M');
INSERT INTO
test (name, gender)
VALUES ('John','F');
INSERT INTO
test (name, gender)
VALUES ('Linda','M');
INSERT INTO
test (name, gender)
VALUES ('Tom','F');
COMMIT;