Элегантный способ удаления строк, на которые не ссылаются другие таблицы

У меня есть две таблицы (Tasks and Timeentries), которые связаны внешним ключом (TimeEntries.TaskID ссылается на Tasks.ID)

Теперь я хотел бы удалить все строки из Tasks, на которые не ссылается таблица TimeEntries. Я думал, что это должно сработать:

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

Но он влияет на 0 строк, хотя в таблице "Задачи" есть много строк без ссылок.

В чем может быть проблема? Конечно, я мог бы написать SP, который выполняет итерацию всех строк, но похоже, что это можно сделать в одном лайнере.

Я предполагаю, что это одна из этих ошибок при низком потоке. Пожалуйста, помогите!

Ответы

Ответ 1

Там есть одна печально известная версия для not in. В принципе, id not in (1,2,3) является сокращением для:

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

Теперь, если ваша таблица TimeEntries содержит любую строку с TaskID of null, not in преобразуется в:

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

Результат сравнения с null всегда unknown. Поскольку unknown не является истинным в SQL, предложение where отфильтровывает все строки, и вы ничего не теряете.

Легкое исправление - это дополнительное предложение where в подзапросе:

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

Ответ 2

В одном случае это позаботится о "проблеме", которую вы имеете с нулями (см. ссылку ниже для получения дополнительной информации)

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

Чтобы понять проблему, которую вы имеете, посмотрите Выберите все строки из одной таблицы, которые не существуют в другой таблице

Ответ 3

Поскольку вы используете 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)

SQL Above должен удалить все строки из задач, в которых Task.ID не существует в таблице записей времени. Я бы запускал его как select Statement для тестирования:)

Ответ 5

Я знаю, что это старо, но мне интересно, почему никто не упомянул запрос на удаление, как описано здесь. Итак, для справки:

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

Этот синтаксис не совместим с ISO, поэтому он будет работать только для T-SQL.