Updating an empty character column
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Updating an empty character column

Hybrid View

  1. #1
    Join Date
    Nov 2003
    Posts
    31

    Updating an empty character column

    Hi,

    I have a question regarding the default value pf a CHAR(1) column.

    My apologies if this is really obvious. I was just not able to find
    an appropriate answer in my searches.

    I have a table with a CHAR(1) field in it. This has already
    some data in it. I simply need to set the CHAR(1) field to 'N'
    whenever it is null.

    I altered the table so It will have a
    default value for the column. So, any data entered from now on
    should be fine. However, the data alterady present in the data
    needs to be modified as well.

    Here are the sample statements:

    SQL> create table test ( a int, b char(1));

    Table created.

    SQL> insert into test (a) values (9);

    1 row created.

    SQL> insert into test (a) values (99);

    1 row created.

    SQL> alter table test modify b default 'N';

    Table altered.

    SQL> insert into test (a) values (999);

    1 row created.

    SQL> insert into test (a) values (9999);

    1 row created.

    SQL> select * from test;

    A B
    ---------- -
    9
    99
    999 N
    9999 N

    SQL> update test set b='N' where b!='N';

    0 rows updated.

    SQL> update test set b='N' where b='';

    0 rows updated.

    SQL> update test set b='N' where b=' ';

    0 rows updated.

    SQL> update test set b='N' where b=NULL;

    0 rows updated.

    SQL> update test set b='N' where b='NULL';

    0 rows updated.



    The following seems to work.

    SQL> update test set b='N' where a=9;

    1 row updated.

    SQL> update test set b='N' where a=99;

    1 row updated.

    SQL> select * from test;

    A B
    ---------- -
    9 N
    99 N
    999 N
    9999 N

    However, I cannot afford to the same with my real data
    as there are many rows and the value of column A is not unique. In other
    words, I could have records with values as follows:

    9 A
    9 N
    9 U

    So, I cannot afford to update all columns where A=9 and set them to N.
    Any suggestions would be greatly appreciated.

    Thank you,
    Sankar.
    Sankar B. Mandalika

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Ah hah ... what you want is the predicate:
    Code:
    where b Is Null
    the opposite of which is
    Code:
    where b Is Not null
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,015
    Quote Originally Posted by slimdave
    Ah hah ... what you want is the predicate:
    Code:
    where b Is Null
    the opposite of which is
    Code:
    where b Is Not null
    I know you know the answer, but just to be painfully obvious I created a small demonstration of the
    difference between NULL and 'NULL'. Parden my overzealousness.

    Code:
      1  SELECT '|' || ASCII(NULL) || '| != ' ||
      2                ASCII('N')  || ' + '   ||
      3                ASCII('U')  || ' + '   ||
      4                ASCII('L')  || ' + '   ||
      5                ASCII('L') "NULL != 'NULL'"
      6*   FROM dual
    SQL> /
    
    NULL != 'NULL'
    -----------------------
    || != 78 + 85 + 76 + 76
    this space intentionally left blank

  4. #4
    Join Date
    Nov 2003
    Posts
    31
    Thank you very much slimdave and galdolf989 for the solutions. It worked like a charm. Truly appreciate your quick and detailed responses.
    Sankar B. Mandalika

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width