将所有0值设置为NULL

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

如何在SQL表中将所有0值设置为NULL?

Thanks in advance.

提前致谢。

4 个解决方案

#1

update table set col = null where col = 0

#2

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.

但你的意思是在所有专栏中?这将是更多的工作,你只需为每列创建单独的语句。也可以在存储过程和EXEC中读取模式并生成SQL,但我不建议这样做,因为我认为这是一次性的工作,如果你经常这样做,那么设计的东西是错误的。

#3

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

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