how to check for null values in 24 columns in a table

[Ratzie]

I have a table that has 24 columns. They should not allow NULL values, but they do. (Vendor driven)

The problem is if there is a NULL value in the column it will break the app.

I have put in a service request with Vendor, but in the mean time I would like to keep on top of this. The thing is searching thru the 24 columns for NULL values, and if NULL replace with '0'.

How would I write the sql for this?
I got for one column but would I add the other 24?

Need to set values of 0 for anything today and onward... Is sysdate right to use?

update table
set column1 = '0'
where column1 IS NULL
AND appt_date => sysdate

[Peter Godron · Accepted solution]

Hi,
off the top of my head:
update table set col1=nvl(col1,'0'), col2=nvl(col2,'0'), col3=nvl(col3,'0')...;

Please test first.

[Ratzie]

How would I run a select first to check...

[Volker Borowski]

Hi,

to avoid further problems, you should add NOT NULL constraint to the column after updating all values.

update TAB set column1 = '0' where column1 IS NULL;
commit;
alter table TAB modify (column1 not null);

... for all columns to change

Volker

[Indira Aramandla]

Hi LHradowy,

If you need to select to list all the rows with NULL column values, then

select * from table
where col1 is null or
col2 is null or
col3 is null or
like that for all the columns.

or if you need to know how many rows to see that you update the same number of rows, then select count(*) from table where
col1 is null or
col2 is null or
like that for all the columns.

You could exclude the primary key columns as they will not be null.

To update the columns with '0' that are null use the nvl as peter suggested. You can verify the update count with the above select count.


Indira A

[Brian Crabtree]

LHradowy,

If you need to find them, then selecting them specificly is the only way to do it. If you want to just get rid of them:

alter table tableA modify (column1 varchar2(..) default ' ' not null);

That will update the column with a ' ', as well as setting it to NOT NULL.

Brian

[Ratzie]

Thanks for the help