Updating 0 value to Null

Just_flow
Just_flow used Ask the Experts™
on
Hi While I was try to change the 0 value in the column to Null, It gave me this error, if you could advise me with this error, would much appreciate :)

Capture.PNG
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Replaces column does not contain numeric data type values probably. What is the Replaces data type?

You may try following

UPDATE btn_pricing$  SET Replaces = NULL WHERE Replaces = '0'
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015
Commented:
The error occurs because an update action attempted to convert a value to an integer that cannot be converted, i.e. '49377-08120' with the dash makes it non-numeric.  

What is the data type of Replaces?  If that query is indeed the source of an error, then setting it to 0 when it's a character type means implicit conversion, and if any value in that column can't be converted to an int it will throw an error.  If so, then ... WHERE Replaces = '0' would be a better choice.

As an aside, punctuation marks such as $ in table names is not good programming practice, as it forces the developer to use square brackets [ ] surrounding the value, which is one thing to potentially forget and throw an error.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
pcelba's answer should work for you.

Author

Commented:
Thanks guys, I figured it out that I missed ' ' :/

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial