Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

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

SOLVED
Go to solution
Ratzie
Super Advisor

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

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
6 REPLIES
Peter Godron
Honored Contributor
Solution

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

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
Super Advisor

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

How would I run a select first to check...
Volker Borowski
Honored Contributor

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

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
Honored Contributor

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

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
Never give up, Keep Trying
Brian Crabtree
Honored Contributor

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

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
Super Advisor

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

Thanks for the help