Elegant way to delete rows which are not referenced by other table

Question

Hi,

I have two tables (Tasks and Timeentries), which are connected by a foreign key (TimeEntries.TaskID references 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:

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.

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!

Answer 1

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

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 ...

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.

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
       )

Answer 2

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

DELETE  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

Answer 3

  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 :)

Answer 4

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

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