1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

case statement

Discussion in 'SQL PL/SQL' started by bnramesh8, Jul 15, 2014.

  1. bnramesh8

    bnramesh8 Active Member

    Messages:
    24
    Likes Received:
    0
    Trophy Points:
    80
    Hi All,
    I want to update a table using case statement?
    for example in my table i want to change the gender column f->m and m->f using case statement?






    thanks in advance...
     
  2. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi,

    Try something like (with your table name and column):


    Code (SQL):
    UPDATE person
    SET gender = (CASE
              WHEN gender = 'M' THEN 'F'
              ELSE 'M'
             END
            );
    Assuming, of course, that the only 2 possible values for gender are 'M' & 'F' in the table contents.
    If, the field is NULL, the update defaults it to 'M' (ELSE clause of CASE); if you want to change this rule, just add an extra line for the situation: "WHEN gender='F' THEN 'M' ELSE NULL".
     
    bnramesh8 likes this.
  3. Rajesh Sinha

    Rajesh Sinha Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    HYderabad
    you can use where clause too in stead of using CASE statement.. like SET gender = 'M' where gender = 'F'