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.