Updating 60 column of null value ?

  • aloshya

    Ten Centuries

    Points: 1045

    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

  • anthony.green

    SSC Guru

    Points: 112470

    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: 148761

    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: 1045

    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: 1045

    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: 148761

    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

  • anthony.green

    SSC Guru

    Points: 112470

    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: 1045

    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: 1045

    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: 1004474

    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: 148761

    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: 2004

    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

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

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