Solved

SQL 2000: Change all values of a column to 0 where the value is currently NULL

Posted on 2007-03-23
4
2,450 Views
Last Modified: 2008-04-09
I have a table entitled insurance_details_tbl that contains several float columns - I woult like to change all values in each column that are NULL to 0.

I have the below query (doing it column by column currently), which executes without error but changes nothing:

UPDATE    insurance_details_tbl
SET              value_computer = 0
WHERE     (value_computer = 'NULL')
0
Comment
Question by:wbstech
  • 2
4 Comments
 
LVL 15

Expert Comment

by:Colosseo
ID: 18778982
try it with out the ' around null (this makes it a string comparison)

UPDATE    insurance_details_tbl
SET              value_computer = 0
WHERE     (value_computer = NULL)
0
 
LVL 4

Author Comment

by:wbstech
ID: 18778984
Ah - copied and pasted the wrong one. Already tried that - same result.
0
 
LVL 15

Accepted Solution

by:
Colosseo earned 250 total points
ID: 18779000
try

UPDATE    insurance_details_tbl
SET              value_computer = 0
WHERE     (value_computer IS NULL)

Scott
0
 
LVL 11

Expert Comment

by:Sven
ID: 18779341
Or for more than one column in one statement:

UPDATE table
SET column1 = isnull(column1,0), column2 = isnull(column2,0)

Beware that this UPDATE will update ALL rows indenent if there a NULL values in the columns or not. But only NULL values will change to 0, all other values are updated with theire own value.
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now