删除其他表未引用的行的优雅方式 [英] Elegant way to delete rows which are not referenced by other table

问题描述

我有两个表(Tasks 和 Timeentries)，它们通过外键连接(TimeEntries.TaskID 引用 Tasks.ID)

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

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

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

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

但它影响了 0 行，即使 Tasks 表中有很多未引用的行.

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

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

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.

我想这是睡眠时间下溢错误之一.请帮忙！

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

推荐答案

not in 有一个臭名昭著的陷阱.基本上，id not in (1,2,3) 是:

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

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

现在，如果您的 TimeEntries 表包含 TaskID 为 null 的任何行，not in 将转换为:

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

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

与 null 比较的结果总是 unknown.由于 unknown 在 SQL 中不为真，where 子句过滤掉所有行，最终不会删除任何内容.

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.

一个简单的解决方法是在子查询中增加一个 where 子句:

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

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

这篇关于删除其他表未引用的行的优雅方式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！