Solved

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

Posted on 2007-03-23
4
2,452 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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

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 …
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 …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

820 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