Solved

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

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
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…

809 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