Запрос на удаление записей в одной таблице с использованием предложения «НЕ В» для проверки с несколькими таблицами. Можно ли упростить этот запрос?
Прямо сейчас я использую следующий запрос
string myQry = "DELETE FROM myMainTbl AS a WHERE
a.id NOT IN(SELECT b.mainid FROM mySubTbl_1 AS b WHERE b.mainid = a.id GROUP BY b.mainid HAVING MAX(b.sttime) > @mydate)
AND
a.id NOT IN(SELECT c.mainid FROM mySubTbl_2 AS c WHERE c.mainid = a.id GROUP BY c.mainid HAVING MAX(c.sttime) > @mydate)
AND
a.id NOT IN(SELECT d.mainid FROM mySubTbl_3 AS d WHERE d.mainid = a.id GROUP BY d.mainid HAVING MAX(d.sttime) > @mydate)
AND
a.id NOT IN(SELECT e.mainid FROM mySubTbl_4 AS e WHERE e.mainid = a.id GROUP BY e.mainid HAVING MAX(e.sttime) > @mydate)
AND
a.id NOT IN(SELECT f.mainid FROM mySubTbl_5 AS f WHERE f.mainid = a.id GROUP BY f.mainid HAVING MAX(f.sttime) > @mydate)";
Можно более упрощенно?
Насколько я знаю, MS Access не поддерживает именованные пареметры в стиле T-SQL (например, @mydate), поэтому ваш запрос все равно не будет работать. Подтвердите, что вы используете JET Red или ACE в MS Access или используете Access в качестве внешнего интерфейса для SQL Server или другой базы данных ODBC/OLE-DB.
Похоже, вы хотите предотвратить непреднамеренное удаление основных сущностей в отношениях внешнего ключа. Способ сделать это — применить ограничения FK — таким образом вам не нужно фильтровать оператор DELETE: вместо этого СУБД просто выдаст ошибку, если нарушит ограничение.
Ответы 2
Я рекомендую NOT EXISTS над NOT IN. Это выглядит так:
NOT EXISTS (SELECT 1 FROM mySubTbl_1 AS b WHERE b.mainid = a.id AND b.sttime > @mydate)
Если после даты не существует соответствующей строки, то и максимум не существует.
Затем вы можете добавить индексы для оптимизации этого запроса: mySubTbl_1(mainid, sttime).
С точки зрения вашего запроса создайте индексы и используйте:
DELETE FROM myMainTbl AS a
WHERE NOT EXISTS (SELECT b.mainid FROM mySubTbl_1 AS b WHERE b.mainid = a.id AND b.sttime > @mydate) AND
NOT EXISTS (SELECT c.mainid FROM mySubTbl_2 AS c WHERE c.mainid = a.id AND c.sttime > @mydate) AND
NOT EXISTS (SELECT d.mainid FROM mySubTbl_3 AS d WHERE d.mainid = a.id AND d.sttime > @mydate) AND
NOT EXISTS (SELECT e.mainid FROM mySubTbl_4 AS e WHERE e.mainid = a.id AND e.sttime > @mydate) AND
NOT EXISTS (SELECT f.mainid FROM mySubTbl_5 AS f WHERE f.mainid = a.id AND f.sttime > @mydate)";
Вы можете переписать это в NOT EXISTS, чтобы не беспокоиться о группах и агрегировании, а также объединить данные для упрощения запроса:
DELETE
FROM myMainTbl AS a
WHERE
NOT EXISTS(
SELECT 1 FROM (
SELECT mainid, sttime
FROM mySubTbl_1
UNION ALL
SELECT mainid, sttime
FROM mySubTbl_2
UNION ALL
SELECT mainid, sttime
FROM mySubTbl_3
UNION ALL
SELECT mainid, sttime
FROM mySubTbl_4
UNION ALL
SELECT mainid, sttime
FROM mySubTbl_5
) b
WHERE b.mainid = a.id AND sttime > @mydate
)
Это также семантически намного более ясно для меня, вы хотите удалить все строки, в которых нет связанной строки в одной из подтаблиц со временем, превышающим ваш параметр.
Большое спасибо, он работает красиво, просто и быстрее с помощью Union.
Краткий ответ: вы не можете. MS Access не полностью реализует SQL-92 (даже Access 2019!) и не поддерживает использование
JOINвDELETE, см. здесь: stackoverflow.com/questions/5585732/…