Updating 60 column of null value ?

Question

Updating 60 column of null value ?

aloshya

Ten Centuries

Points: 1105
More actions
August 25, 2017 at 2:04 am

#328057

I have table as below with 63 columns and 75 rows records and while trying to sum the columns its returing the result as null . There are many null values in many column and its in no order
how to replace all the null value in my null fields with some numeric value

Regards

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Ant-Green SSC Guru Points: 113445 More actions · Answer 1

aloshya - Friday, August 25, 2017 2:04 AM
I have table as below with 63 columns and 75 rows records and while trying to sum the columns its returing the result as null . There are many null values in many column and its in no order
how to replace all the null value in my null fields with some numeric value

Regards

Are you sure you want to put a value into a null cell? NULL in itself means unknown so how do you know the value to put into the cell?

If you do you will need to write a script which update where the column is null 63 times something like the below replacing the 0 for whatever the value is you want to update the NULL to.

UPDATE tab1 SET col1 = ISNULL(col1,0), col2 = ISNULL(col2,0), .....

You maybe best doing the ISNULL parts in your selects rather then modifying the schema of your table.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

Yes, and if you do update the table so that there are no more NULLs, make sure you also update the table DDL so that all columns are defined as NOT NULL. That'll stop any more NULLs creeping into your table.

John

Edit - corrected typo

aloshya Ten Centuries Points: 1105 More actions · Answer 3

anthony.green - Friday, August 25, 2017 2:41 AM
aloshya - Friday, August 25, 2017 2:04 AM
I have table as below with 63 columns and 75 rows records and while trying to sum the columns its returing the result as null . There are many null values in many column and its in no order
how to replace all the null value in my null fields with some numeric value

Regards
Are you sure you want to put a value into a null cell? NULL in itself means unknown so how do you know the value to put into the cell?
If you do you will need to write a script which update where the column is null 63 times something like the below replacing the 0 for whatever the value is you want to update the NULL to.
UPDATE tab1 SET col1 = ISNULL(col1,0), col2 = ISNULL(col2,0), .....
You maybe best doing the ISNULL parts in your selects rather then modifying the schema of your table.

i want to sum the total column in each row . but null is giving the total result as NULL
is there anyway to sum the total here

aloshya Ten Centuries Points: 1105 More actions · Answer 4

John Mitchell-245523 - Friday, August 25, 2017 3:53 AM
Yes, and if you do update the table so that there are no more NULLs, make sure you also update the table DDL so that all columns are defined as NOT NULL. That'll stop any more NULLs creating into your table.
John

i don't know if updating is the right method and it seems long process. we have 63 columns

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

If you're intepreting NULL as 0, then:

SELECT COALESCE(col1,0) + COALESCE(col2,0) + ... AS SumofAllCols FROM MyTable

(You can also use ISNULL instead of COALESCE. It's a matter of personal preference in this case.)

John

Ant-Green SSC Guru Points: 113445 More actions · Answer 6

aloshya - Friday, August 25, 2017 4:17 AM
i want to sum the total column in each row . but null is giving the total result as NULL
is there anyway to sum the total here

Take a look at the function ISNULL() as per my update query. It will substitute the NULL value for a value you specify. OK you will need to have 60 ISNULL()'s in your SUM query but that would be the way to do it without table updates.

aloshya Ten Centuries Points: 1105 More actions · Answer 7

anthony.green - Friday, August 25, 2017 4:24 AM
aloshya - Friday, August 25, 2017 4:17 AM
i want to sum the total column in each row . but null is giving the total result as NULL
is there anyway to sum the total here
Take a look at the function ISNULL() as per my update query. It will substitute the NULL value for a value you specify. OK you will need to have 60 ISNULL()'s in your SUM query but that would be the way to do it without table updates.

How to do it through table updates , is there any features in the table which will never store any null values. ?

aloshya Ten Centuries Points: 1105 More actions · Answer 8

John Mitchell-245523 - Friday, August 25, 2017 4:24 AM
If you're intepreting NULL as 0, then:
SELECT COALESCE(col1,0) + COALESCE(col2,0) + ... AS SumofAllCols FROM MyTable
(You can also use ISNULL instead of COALESCE. It's a matter of personal preference in this case.)
John

Its long query if we updated each column with the update statement and is there any way to minimize the length of query

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Yes, make the column NOT NULL, then any attempt to insert/update to NULL will throw an error.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 10

aloshya - Friday, August 25, 2017 4:36 AM
John Mitchell-245523 - Friday, August 25, 2017 4:24 AM
If you're intepreting NULL as 0, then:
SELECT COALESCE(col1,0) + COALESCE(col2,0) + ... AS SumofAllCols FROM MyTable
(You can also use ISNULL instead of COALESCE. It's a matter of personal preference in this case.)
John
Its long query if we updated each column with the update statement and is there any way to minimize the length of query

It is a long query, yes. I suspect that's something to do with poor database design. But does that matter? Stick it in a stored procedure or view and you won't have to look at it. Alternatively, do what Gail and I have both suggested, and make your columns not nullable.

John

Kenny Jozi SSCrazy Points: 2027 More actions · Answer 11

Why Would you want to do that instead of using SQL functions to do your SUM ?

You can very easily generate SQL to do the updates:
DECLARE @table nvarchar(100) , @value INT

--- table name to update SET @table = 'YourTable' -- set value to Your Table name SET @value = 1

SELECT 'UPDATE ['+ TABLE_SCHEMA + '].['+ TABLE_NAME + '] SET [' + COLUMN_NAME + '] = '+ CAST(@value as nvarchar(3)) + ' WHERE [' + COLUMN_NAME + '] IS NULL OR [' + COLUMN_NAME + '] = NULL' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @table AND IS_NULLABLE = 'YES' AND IS_NULLABLE = 'YES' AND DATA_TYPE NOT IN ('datetime2','datetime','smalldatetime','nvarchar')

Replace "YourTable" with the name of your table, and run. run the output into New Query window , you cal also modify the query to meet your needs .

SQL 2000/2005/2008/2012 DBA - MCTS/MCITP