Set all the 0 values to NULL

Question

How can I set all the 0 values to NULL in an SQL table ?

Thanks in advance.

Answer 1

UPDATE MYTABLE SET MYCOLUMN = NULL WHERE MYCOLUMN = 0

But do you mean in all columns? That will be more work and best you just create separate statements for each column. It is also possible to read the schema and generate an SQL in a stored procedure and EXEC that but I do not recommend since I imagine this is a one-off job and if you are doing this more often then something with the design is wrong.

Answer 2

UPDATE MyTable
SET Col1 = NULLIF(Col1, 0),
    Col2 = NULLIF(Col2, 0),
    Col3 = NULLIF(Col3, 0)
WHERE (Col1 = 0 
    OR Col2 = 0 
    OR Col3 = 0)

Answer 3

update table set col = null where col = 0

Answer 4

If it is a one time job, you could always grab the output from following statement and execute that.

SELECT  'UPDATE ' 
        + OBJECT_NAME(OBJECT_ID)
        + ' SET '
        + sc.name
        + ' = NULL WHERE '
        + sc.name
        + ' = 0'        
FROM    sys.columns sc 
        INNER JOIN sys.types st ON st.system_type_id = sc.system_type_id
WHERE   st.name IN ('bigint', 'int', 'smallint')        
        AND OBJECT_NAME(OBJECT_ID) = 'YourTable'