Изящный способ удалить строки, на которые не ссылается другая таблица
У меня есть две таблицы (Tasks и Timeentries), которые соединены внешним ключом (TimeEntries. Ссылочные Задачи TaskID. Идентификатор)
Теперь я хотел бы удалить все строки из Задач, на которые не ссылается таблица TimeEntries. Я думал, что это должно работать:
DELETE FROM Tasks WHERE ID not IN (SELECT TaskID FROM TimeEntries)
Но это влияет на 0 строк, даже при том, что существует много не имеющих ссылки строк в таблице Tasks.
Какова могла бы быть проблема здесь? Конечно, я мог записать SP, который выполняет итерации всех строк, но кажется, что это могло быть сделано в одном лайнере.
Я предполагаю, что это - одна из тех ошибок потери значимости sleeptime. Помогите!
4 ответа
Есть одна печально известная ошибка для , которой нет в . По сути, id не в (1,2,3) является сокращением для:
id <> 1 and id <> 2 and id <> 3
Теперь, если ваша таблица TimeEntries содержит любую строку с TaskID из null , не в переводится в:
ID <> null and ID <> 1 and ID <> 2 AND ...
Результат сравнения с null всегда неизвестен . Поскольку unknown неверно в SQL, предложение where отфильтровывает все строки, и в итоге вы ничего не удаляете.
Простое исправление - это дополнительное предложение where в подзапросе:
DELETE FROM Tasks
WHERE ID not IN
(
SELECT TaskID
FROM TimeEntries
WHERE TaskID is not null
)
С одной стороны, это решит «проблему», которая у вас возникла с нулями (см. Ссылку ниже для получения дополнительной информации)
DELETE FROM Tasks
WHERE NOT EXISTS (SELECT 1 FROM TimeEntries
WHERE TimeEntries.TaskID = Tasks.ID )
Чтобы понять, с какой проблемой вы столкнулись, взгляните на Выбрать все строки из одной таблицы, которых нет в другой таблице.
Delete FROM Tasks
WHERE not Exists
(SELECT 'X' FROM TimeEntries where TimeEntries.TaskID = Tasks.ID)
Приведенный выше SQL должен удалить все строки из задач, для которых Task.ID не существует в таблице записей времени. Я бы сначала запустил его как оператор select для тестирования :)
Поскольку вы работаете с SQL 2008, вы можете использовать новый изящный синтаксис слияния.
MERGE Tasks AS target
USING TimeEntries as Source ON (Target.TaskID=Source.TaskID)
WHEN NOT MATCHED BY Source THEN DELETE;