Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    Join Date
    Sep 2009
    Posts
    90

    Question Unanswered: Update Null Values

    I have an existing table which has NULL values. I want those NULL values to be a 0 numerical value. Please help me!

    Thanks in advance!

  2. #2
    Join Date
    Sep 2009
    Posts
    90
    please help me

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Code:
    SELECT COALESCE(nullableColumn, 0)
    FROM MyTable
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Are you sure you want them to be zero?

    Do you understand what a NULL is, why we use them and what they represent?
    George
    Home | Blog

  5. #5
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    Wim's sql will convert null to 0 when reading from the table not change the data in the table if you really want to change the table then

    UPDATE YourTable
    SET YourField = 0
    WHERE YourField is null

    should do the trick, however if that is what you want then i would alter the table definition to not allow nulls and a default of 0, that will then do the same thing and stop nulls entering the table in the first place
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  6. #6
    Join Date
    Sep 2009
    Posts
    90
    I have integer values and I cant get the sum of rows when there are nulls

  7. #7
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    well then Wim's will work as well as

    Select Sum(YourField) as total
    from YourTable
    Where YourField is not Null

    if you are summing multiple columns then Wims is better, if not then the above is better this is because mine will remove rows with null, so if upi have

    col 1, col 2
    1,4
    2,3
    null,2
    4,null

    mine will produce 3,7 as this is the sum of rows without nulls, wims will be 7,9
    Last edited by m.timoney; 09-30-09 at 13:14.
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by rvr707
    I have integer values and I cant get the sum of rows when there are nulls

    That's not TRUE
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    Quote Originally Posted by Brett Kaiser
    That's not TRUE
    i'm assuming that he's hitting a DBNULL error in external code
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  10. #10
    Join Date
    Sep 2009
    Posts
    90
    Because everytime i get the sum of rows with nulls, it also returns a null result.
    Like this one:

    Code:
    Col1 --- Col2 --- Col3 --- Col4 --- Col5
    120  --- 455  ---  456 ---  122 --- 100
    111  --- NULL --- 111 ---  456 ---  NULL
    222  --- 142  ---  100 ---  NULL--- NULL
    333  --- 455  ---  700 ---  NULL---600
    456  --- 111  ---  707 --- 100   --- 124
    And then when I do this:

    Code:
    Select Col1+Col2+Col3+Col4+Col5 as 'Total'
    From tbl
    
    Total
    1253
    NULL
    NULL
    NULL
    1498

    The rows that has NULL values also results with NULL.
    So I need to change all NULL values to 0 to have the correct sum.
    Last edited by rvr707; 10-07-09 at 11:14.

  11. #11
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    thats because you are adding the results not SUM'ing them

    Select Sum(Val)
    from (Select 1 as Val Union All Select 2 Union All Select 3 Union All Select null) as t

    =6

    Select 1+2+3+null

    =null

    as said in your other thread if you fix the structure then you just do

    Select Sum(value)
    From table
    Group By Product

    And everything works, with the bad structure your using everything will be 4 times harder to do and be like pulling teeth to get it to function right

    it's the difference between these 2
    Last edited by m.timoney; 10-07-09 at 11:47.
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  12. #12
    Join Date
    Sep 2009
    Posts
    90
    Quote Originally Posted by m.timoney
    thats because you are adding the results not SUM'ing them

    Select Sum(Val)
    from (Select 1 as Val Union All Select 2 Union All Select 3 Union All Select null) as t

    =6

    Select 1+2+3+null

    =null

    as said in your other thread if you fix the structure then you just do

    Select Sum(value)
    From table
    Group By Product

    And everything works, with the bad structure your using everything will be 4 times harder to do and be like pulling teeth to get it to function right

    it's the difference between these 2

    Thanks! It helped!
    The reason why I cant change the structure is that im getting the data from old excel database files and just made a program to import the data to ms sql.

  13. #13
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    well then in that case create a new structure along side the old, and then import the data from the old structure into the new, you can do it with 1 insert query per column

    so using
    Quote Originally Posted by m.timoney
    ...
    so you have your Product table that contains
    Product ID, and anyother product information

    your MDC Table that has your MDC codes

    and your data table that contains
    MDC Code, Product ID, Value
    ...
    so with that structure you then run
    INSERT INTO dataTable
    (MDC Code, Product ID, Value)
    SELECT MDC Code, <<Product ID as value>>, <<Product Name as column header>>
    FROM XLbasedTable

    and a trick i do is to dump the query text into excell and then concat it with the columns i need and then paste the values into a query window so that with very little effort you can generate all of them

    once you have done this you have fixed the structure and now have the data is the perfect DB format to make the rest of this work is a piece of cake
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  14. #14
    Join Date
    Sep 2009
    Posts
    90
    That's exactly what Im doing right now. Thnx a lot!

  15. #15
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    once thats done hide the XL spread sheet in the deepest darkest archive you can find and then pretend it never existed
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •