ИСПОЛЬЗОВАНИЕ ПОДЗАПРОСОВ С DELETE
Вы можете также использовать подзапросы в предикате команды DELETE. Это даст вам возможность определять некоторые довольно сложные критерии, чтобы установить, какие строки будут удаляться, что важно, так как вы, конечно же, не захотите по неосторожности удалить нужную строку. Например, если мы закрыли наше ведомство в Лондоне, мы могли бы использовать следующий запрос чтобы удалить всех заказчиков, назначенных продавцам в Лондоне:
DELETE FROM Customers WHERE snum = ANY (SELECT snum FROM Salespeople WHERE city = 'London');
Эта команда удалит из таблицы Заказчиков строки Hoffman и Clemens (назначенных для Peel), и Pereira (назначенного для Motika). Конечно, вы захотите удостовериться, правильно ли сформирована эта операция, прежде чем удалить или изменить строки Peel и Motika.
Это важно. Обычно, когда мы делаем модификацию в базе данных, которая повлечет другие модификации, наше первое желание - сделать сначала основное действие, а затем проследить другие, вторичные. Этот пример, покажет, почему более эффективно делать наоборот, выполнив сначала вторичные действия.
Если, например, вы решили изменить значения полей city ваших продавцов везде, где они переназначены, вы должны рассмотреть всех этих заказчиков более сложным способом.
Так как реальные БД имеют тенденцию разрастаться до значительно больших размеров, чем наши небольшие типовые таблицы, это может стать серьезной проблемой. SQL может предоставить некоторую помощь в этой области, используя механизм справочной целостности (обсуждённый в Главе 19), но это не всегда доступно и не всегда применимо.
Хотя вы не можете обращаться к таблице, из которой вы будете удалять строки, в предложении FROM подзапроса, вы можете в предикате сослаться на текущую строку-кандидат этой таблицы, которая является строкой, которая проверяется в основном предикате. Другими словами, вы можете использовать соотнесённые подзапросы. Они отличаются от тех соотнесённых подзапросов, которые вы могли использовать с INSERT, в котором они фактически базировались на строках-кандидатах таблицы, задействованной в команде, а не на запросе другой таблицы.
DELETE FROM Salespeople WHERE EXISTS (SELECT * FROM Customers WHERE rating = 100 AND Salespeople.snum = Customers.snum);
Обратите внимание, что часть AND предиката внутреннего запроса обращается к таблице Продавцов. Это означает, что весь подзапрос будет выполняться отдельно для каждой строки таблицы Продавцов, так же, как это выполнялось с другими соотнесенными подзапросами. Эта команда удалит всех продавцов, которые имели по меньшей мере одного заказчика с оценкой 100 в таблице Продавцов. Конечно же, имеется другой способ сделать то же:
DELETE FROM Salespeople WHERE 100 IN (SELECT rating FROM Customers WHERE Salespeople.snum = Customers.snum);
Эта команда находит все оценки для каждого заказчика продавцов и удаляет тех продавцов, заказчики которых имеют оценку = 100.
Обычно соотнесённые подзапросы это подзапросы, связанные с таблицей, к которой они ссылаются во внешнем запросе (а не в самом предложении DELETE), и так же часто используемые. Вы можете найти наименьший заказ на каждый день и удалить продавцов, которые произвели его, с помощью следующей команды:
DELETE FROM Salespeople WHERE snum IN (SELECT snum FROM Orders WHERE amt = (SELECT MIN (amt) FROM Orders b WHERE a.odate = b.odate));
Подзапрос в предикате DELETE принимает соотнесённый подзапрос. Этот внутренний запрос находит минимальный заказ суммы приобретений для даты каждой строки внешнего запроса. Если эта сумма - такая же, как и сумма текущей строки, предикат внешнего запроса верен, что означает, что текущая строка имеет наименьший заказ для этой даты. Поле snum продавца, ответственного за этот заказ, извлекается и передается в основной предикат команды DELETE, которая затем удаляет все строки с этим значением поля snum из таблицы Продавцов (так как snum это первичный ключ таблицы Продавцов, то, естественно, там должна иметься только одна удаляемая строка для значения поля snum, выведенного с помощью подзапроса. Если имеется больше одной строки, все они будут удалены.) Поле snum = 1007, которое будет удалено, имеет наименьшее значение на 3 октября; поле snum = 1002, наименьшее на 4 октября; поле snum = 1001, наименьшее в заказах на 5 октября (эта команда кажется довольно грубой, особенно когда она удаляет Peel создавшего единственный заказ на 5 октября, но зато это хорошая иллюстрация).
Если вы хотите сохранить Peel, вы могли бы добавить другой подзапрос, который сделал бы это:
DELETE FROM Salespeople WHERE snum IN (SELECT snum FROM Orders a WHERE amt = (SELECT MIN (amt) FROM Orders b WHERE a.odate = b.odate) AND 1 < (SELECT COUNT onum FROM Orders b WHERE a.odate = b.odate));
Теперь для дня, в котором был создан только один заказ, будет произведен счёт = 1 во втором соотнесённом подзапросе. Это сделает предикат внешнего запроса неправильным, и поля snum, следовательно, не будут переданы в основной предикат.
