Replace a column values from 'male' to 'female' and 'female' to 'male'

Question

I have one table, gender, in which only two entries are there, 'male' and 'female' number of rows with these two entries only. Now I want to write a procedure which replaces these two values and the output should be as below,

Input           Output

sex             sex
------------------------
male            female
female          male
female          male
male            female

I am creating a procedure, which gives me an error. I am using a cursor to fetch more than one row. I know that we can do it by using only one update statement, but I want to try like this.

declare
  v_gen gender%rowtype;
  cursor cur_gender is
    select * from gender;
begin
  open cur_gender;
  loop
    fetch cur_gender into v_gen;
    select * from gender;
    if v_gen='male'
    then
      update gender set sex='female';
    else
      update gender set sex='male';
    end if;
    exit when v_gen%notfound;
  end loop;
  close cur_gender;
end;

Cornela

@parado PLS-00306 wrong numbers or types of arguments in call to '=' PLS-00324 cursor attribute may not be applied to non-cursor 'v_gen' – Cornela

don't know the solution, but i would be very careful updating all rows as you are doing with the statement (I am assuming this isn't the intention): update gender set sex='female'. And by the way don't use sms language – Dolf

What you do is the following: You look at the first row only (with your FETCH .. INTO) then you update all rows. I doubt thats intended... – Jennettejenni

Answer 1

update Table1 set "col" = (case "col" when 'male' then 'female'
else 'male' end);

fiddle

Convex

OP said I knw that we can do it by using only 1 update statemen – Disappear

Answer 2

ID   Username   Email              Gender
1    sanjay     [email protected]   Male
2    nikky      [email protected]    Male
11   raj        [email protected]      Female
12   Rahul      [email protected]    Female

update users set gender = (case when gender = 'Male' then 'Female' else 'Male' end);

Cumae

Answer 3

There are a number of issues with what you have. The immediate cause of the PLS-00324 is that v_gen is a record, so you need to compare a field with that record to your fixed value, not the whole record:

if v_gen='male'

becomes

if v_gen.sex = 'male'

You then have a spurious select * from gender which looks like an accident, and needs to be removed completely.

And finally you're applying %notfound to the record not to the cursor:

exit when v_gen%notfound;

becomes

exit when cur_gender%notfound;

Well, not really finally, because as others have noted each of your update statements is updating all rows in the table, so you'll end up with everything as either male or female depending on which row it processes last. You need to identify the specific row you want to update. One way is to query the rowid as part of the cursor, but there's a built-in way that is easier:

declare
  cursor cur_gender is
    select * from gender
    for update;
begin
  for v_gen in cur_gender
  loop
    if v_gen.sex='male' 
    then
      update gender set sex = 'female'
      where current of cur_gender;
    else
      update gender set sex = 'male'
      where current of cur_gender;
    end if;
  end loop;
end; 
/

SQL Fiddle.

Or you can simplify it a bit by using case instead of the if:

declare
  cursor cur_gender is
    select * from gender
    for update;
begin
  for v_gen in cur_gender
  loop
    update gender
    set sex = case v_gen.sex when 'male' then 'female' else 'male' end
    where current of cur_gender;
  end loop;
end; 
/

... which is getting pretty close to the single-update-statement approach that you already know about. SQL Fiddle for that version.

You can read more about for update and where current of in the documentation.

Onassis

Answer 4

UPDATE table 
SET    gender = CASE gender 
                  WHEN 'Male' THEN 'Female' 
                  WHEN 'Female' THEN 'Male' 
                  ELSE gender 
                END; 

Tilla

From Review: While this code may answer the question, providing additional context regarding why and/or how this code answers the question improves its long-term value. I would recommend you to check SO's official How to Answer article along with the comprehensive blog post from Jon Skeet. – Largescale

Answer 5

In your case I'd recommend using a Record instead of the FETCH:

CREATE OR REPLACE PROCEDURE swap ()
IS
    CURSOR cur_gender IS   
        SELECT * FROM gender;
     rec_gender cur_gender%ROWTYPE;

BEGIN
       FOR rec_gender IN cur_gender
    LOOP
       IF cur_gender%FOUND THEN    
            IF rec_gender.sex = 'male' THEN
                UPDATE gender set sex='female' 
                WHERE idgender = rec_gender.idgender;
            ELSE
                UPDATE gender set sex = 'male' 
                WHERE idgender = rec_gender.idgender;  
            END IF;
            -- COMMIT; --MAYBE?             
       END IF;
    END LOOP;
END swap;
/

Jennettejenni

Answer 6

There is even a simple way of doing this. You can use DECODE to do this in an Oracle database.

UPDATE GENDER SET SEX = DECODE(SEX, 'MALE', 'FEMALE', 'FEMALE', 'MALE');

The above query changes the sex of male to female and female to male.

Carving

That seems much better than using for loops(?). – Myrmecophagous

Answer 7

SQL query to proceed this:

UPDATE table SET gender =
(CASE  WHEN  gender ='male' THEN 'female' else 'male' END)

Apocynaceous

What do you mean by "proceed this"? – Myrmecophagous

Is this a response to another answer? – Myrmecophagous

Answer 8

Please use this query to update Male to Female and Female to Male. Reference Images.

Table data before

Syntax

    UPDATE <TABLE_NAME> set <COL_NAME> = (CASE <COL_NAME> WHEN '<DATA>' THEN'<DATA>' ELSE '<DATA>' END);

Example:

UPDATE users SET gender = (CASE gender WHEN 'Male' THEN 'Female' ELSE'Male' END);

Table data after

Mopey

How is this different from previous answers? – Myrmecophagous

Answer 9

 CREATE OR REPLACE PROCEDURE swap ()
 IS
        CURSOR cur_gender IS   
        SELECT * FROM gender;
 BEGIN
        FOR rec_gender IN cur_gender
        LOOP
               IF rec_gender.sex = 'male' THEN
                     UPDATE gender set sex='female' where sex='male'; 
               ELSE
                     UPDATE gender set sex = 'male' where sex='female';
               END IF;
       END LOOP;
END swap;

Deci

What for do you use the cursor at all? – Jennettejenni

declare v_gen gender.sex%type; cursor cur_gender is select sex from gender; begin open cur_gender; loop fetch cur_gender into v_gen; if v_gen='male' then --dbms_output.put_line('It was ' ||v_gen); update gender set sex='female'; elsif v_gen='female' then --dbms_output.put_line('It was ' ||v_gen); update gender set sex='male'; end if; exit when cur_gender%notfound; end loop; close cur_gender; end; Here it works but if 1st row is male then it sets male to all rows or vice versa, whts wrong in this – Cornela

Answer 10

Simple create a table, let it be people:

create table people(gender varchar2(10));

insert into people values('&gender');

Suppose inputs are male, female, female, male:

select * from people;
GENDER
    male
    female
    female
    male

select decode(gender, 'male', 'female', 'female', 'male') sex from people;
SEX
    female
    male
    male
    female

Mohur

Answer 11

/*Logic*/

SELECT Name, CASE
  WHEN Gender = 'Male' THEN 'Female'
  ELSE 'Male'
  END
AS "NewGender"
FROM NameGender ;

/*Here NameGender is the name of the Table*/

Englishry

Answer 12

update Employee
set Gender= case Gender when 'Male' then 'Female' when 'Female' then 'Male'
else 
Gender
end

Spermatid

Answer 13

The below query works well in Amazon Redshift.

update Table1 set gender = 
(
    select case when gender = 'male' then 'female'
                when gender = 'female' then 'male' end
)

Bremer

Answer 14

In a simple way:

UPDATE users SET gender = IF(sex='Male','Female','Male');

definition:

IF(condition, value_if_true, value_if_false)

Dewitt