删除其他表未引用的行的优雅方式

I have two tables (Tasks and Timeentries), which are connected by a foreign key (TimeEntries.TaskID references Tasks.ID)

我有两个表(任务和时间项)，它们通过外键(时间项)连接。TaskID引用Tasks.ID)

Now I'd like to delete all rows from Tasks which are not referenced by the TimeEntries table. I thought that this should work:

现在我想从任务中删除所有未被TimeEntries表引用的行。我认为这应该行得通:

DELETE FROM Tasks WHERE ID not IN (SELECT TaskID FROM TimeEntries)

But it affects 0 rows, even though there are a lot of unreferenced rows in the Tasks table.

但是它影响0行，即使任务表中有很多未引用的行。

What might be the problem here? Of course I could write an SP which iterates all rows, but it seems like this could be done in a one liner.

这里的问题是什么?当然，我可以编写一个SP来迭代所有的行，但这似乎可以在一行中完成。

I guess this is one of those sleeptime underflow errors. Please help!

我猜这是睡眠不足的错误之一。请帮助!

5 个解决方案

#1

There's one notorious gotcha for not in. Basically, id not in (1,2,3) is shorthand for:

有一个臭名昭著的陷阱就是“不加入”。基本上，id not in(1,2,3)是:

id <> 1 and id <> 2 and id <> 3

Now if your TimeEntries table contains any row with a TaskID of null, the not in translates to:

现在，如果您的TimeEntries表包含任何带有null值的TaskID的行，则not in的意思是:

ID <> null and ID <> 1 and ID <> 2 AND ...

The result of a comparison with null is always unknown. Since unknown is not true in SQL, the where clause filters out all rows, and you end up deleting nothing.

与null比较的结果总是未知的。因为在SQL中unknown是不成立的，所以where子句过滤掉所有的行，最后什么都不删除。

An easy fix is an additional where clause in the subquery:

一个简单的修复是子查询中的where子句:

DELETE FROM Tasks 
WHERE  ID not IN 
       (
       SELECT  TaskID 
       FROM    TimeEntries 
       WHERE   TaskID is not null
       )

#2

One way, this will take care of the 'problem' you are having with nulls (see link below for more info)

一种方法是，这将解决使用null时遇到的“问题”(更多信息请参见下面的链接)

DELETE FROM Tasks 
WHERE NOT EXISTS (SELECT 1 FROM TimeEntries 
                  WHERE TimeEntries.TaskID  = Tasks.ID )

To understand the problem you are having, take a look at Select all rows from one table that don't exist in another table

要理解您遇到的问题，请查看另一个表中不存在的表中的Select all row

#3

Since you are running SQL 2008, you can use the nifty new merge syntax.

因为您正在运行SQL 2008，所以可以使用漂亮的新合并语法。

MERGE Tasks AS target
USING TimeEntries as Source ON (Target.TaskID=Source.TaskID)
WHEN NOT MATCHED BY Source THEN DELETE;

#4

  Delete FROM Tasks 
       WHERE not Exists 
          (SELECT 'X' FROM TimeEntries where TimeEntries.TaskID  = Tasks.ID)

The SQL Above should delete all the Rows from Tasks where the Task.ID does not exist in the Time Entries Table. I would run it as a select Statement first to test :)

上面的SQL应该从任务所在的任务中删除所有的行。ID在时间条目表中不存在。我将把它作为一个select语句运行，首先测试:)

#5

I know this is old, but I wonder why nobody mentioned a delete query as described here. So, for reference:

我知道这已经过时了，但我想知道为什么这里没有人提到delete查询。所以,供参考:

DELETE FROM Tasks
FROM Tasks LEFT OUTER JOIN
    TimeEntries ON TimeEntries.TaskID = Tasks.ID
WHERE TimeEntries.TaskID IS NULL;

This syntax is not ISO-compatible, so will only work for T-SQL.

这种语法不兼容iso，因此只适用于T-SQL。

#1