Updating 0 value to Null

Just_flow used Ask the Experts™
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 :)

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
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

pcelba's answer should work for you.


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