SQL Server - 删除多列具有空值或零值的行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4962914/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL server - delete rows where multiple columns have either null or zero value
提问by Kathy
I'm a DB newbie, and am struggling with this. I have an existing table with the following columns:
我是 DB 新手，正在为此苦苦挣扎。我有一个包含以下列的现有表：
Showroom, Salesperson, Time1, Time2, Time3, Dte
I'm trying to delete all rows within the table that have either null or zero values in all 3 time columns. I tried the following:
我正在尝试删除表中所有 3 个时间列中具有空值或零值的所有行。我尝试了以下方法：
DELETE FROM myTable
WHERE EXISTS(
SELECT *
FROM myTable
WHERE (Time1 IS NULL OR Time1 = 0)
AND (Time2 IS NULL OR Time2 = 0)
AND (Time3 IS NULL OR Time3 = 0)
)
Thankfully I'm working on a test version of the database, as I wiped out all of the data. Any help would truly be appreciated.
谢天谢地，我正在研究数据库的测试版本，因为我清除了所有数据。任何帮助将不胜感激。
回答by jon3laze
The query should be formatted like this:
查询的格式应如下所示：
DELETE
FROM myTable
WHERE (Time1 IS NULL OR Time1 = 0)
AND (Time2 IS NULL OR Time2 = 0)
AND (Time3 IS NULL OR Time3 = 0)
When doing DELETEstatements I thinkit isalways best to first create your SELECTstatement, and then change it.
在做DELETE陈述，我认为这是最好总是先创建SELECT语句，然后改变它。
SELECT * --If this returns the correct records, simply change to DELETE
FROM myTable
WHERE (Time1 IS NULL OR Time1 = 0)
AND (Time2 IS NULL OR Time2 = 0)
AND (Time3 IS NULL OR Time3 = 0)
回答by Christoph
What you want is just;
你想要的只是;
DELETE myTable
WHERE
(Time1 IS NULL OR Time1 = 0)
AND (Time2 IS NULL OR Time2 = 0)
AND (Time3 IS NULL OR Time3 = 0)
回答by Oded
The EXISTSis superfluous (as is the FROM- it isn't needed for DELETEs):
该EXISTS是多余的（因为是FROM-它不需要DELETES）：
DELETE myTable
WHERE ((Time1 IS NULL OR Time1 = 0)
AND (Time2 IS NULL OR Time2 = 0)
AND (Time3 IS NULL OR Time3 = 0))
回答by nybbler
Try this instead:
试试这个：
DELETE
FROM myTable
WHERE
(Time1 IS NULL OR Time1 = 0) AND
(Time2 IS NULL OR Time2 = 0) AND
(Time3 IS NULL OR Time3 = 0)
The reason all of your records are being deleted is because the EXISTS result is true as long as there exists a single record that has NULL or 0 for all three columns. Since your WHERE clause of the delete statement doesn't identify which records to delete, it is essentially the same as DELETE FROM myTable WHERE 1=1
您的所有记录都被删除的原因是因为只要存在单个记录的所有三列都为 NULL 或 0，则 EXISTS 结果为真。由于 delete 语句的 WHERE 子句不标识要删除哪些记录，它本质上与DELETE FROM myTable WHERE 1=1
回答by Srikanth CHindam
DELETE myTable WHERE
(ISNULL(Time1,0) = 0) AND (ISNULL(Time2,0) = 0) AND (ISNULL(Time3,0) = 0)
