replace NULL with 0.00

i want to replace a table in which columns has <NULL> value with 0.00. How to do that

I tried this way
update OrderSep
Set wless_doll= 0.00
Where wless_doll= NULL

but this doesn't works I dnt know why?.. Can anyone help
LVL 3
ajaypappanAsked:
Who is Participating?
 
HilaireCommented:
>>How can i do it for multiple fields?<<
try

update OrderSep
Set wless_doll = isnull(wless_doll , 0.00),
wless_comm = isnull(wless_comm , 0.00),
wless_charge = isnull(wless_charge , 0.00)
Where wless_doll IS NULL or wless_comm is null or wless_charge is null
0
 
mcmonapCommented:
Hi ajaypappan,

try this instead:

update OrderSep
Set wless_doll = 0.00
Where wless_doll IS NULL
0
 
ajaypappanAuthor Commented:
How can i do it for multiple fields?
suppose i have these columns
wless_doll
wless_comm
wless_charge

How to do all at a time?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
ShogunWadeCommented:
yep as mc says you need to use IS NULL as opposed to = NULL . As NULL  by definition is the absence of a value, by default you can not compare them with =  because = is an equallity check and it is not strictly correct that NULL = NULL (ie an unknown = an unknown)
0
 
hkamalCommented:
ShogunWade, =NULL and IS NULL will both work. It used to be IS NULL was quicker but AFAIK this is no longer the case ..
0
 
ShogunWadeCommented:
UPDATE ordersep
set wless_doll = ISNULL(wless_doll,0),
wless_doll = ISNULL(wless_doll,0),

...etc...
0
 
ShogunWadeCommented:
hkamal ", =NULL and IS NULL will both work."     as I stated     "by default you can not compare them with = "  this istatement s correct.    

It is not ANSI standard to compare NULLS using =    SQL Server gives you the ability to do this by unsing SET ANSI_NULLS OFF    but the ANSI standard is default here!

0
 
hkamalCommented:
Having ISNULL in the SET clause does NOT prevent it updating ALL columns in the SET if ANY of the criteria in the WHERE is satisfied
However, unless you have three (or as many column as you have) successive UPDATEs, you just have to live with it ;-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.