Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2007-03-23
4
Medium Priority
?
2,459 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 1000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

610 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