Понимание SQL

Обратите внимание, что 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 следовательно не будут переданы в основной предикат.

ИСПОЛЬЗОВАНИЕ ПОДЗАПРОСОВ С UPDATE

UPDATE использует подзапросы тем же самым способом что и DELETE - внутри этого необязательного предиката. Вы можете использовать соотнесенные подзапросы или в форме пригодной для использования с DELETE - связаной или с модифицируемой таблицей или с таблицей вызываемой во внешнем запросе. Например, с помощью соотнесенного подзапроса к таблице которая будет модифицироваться, вы можете увеличить комиссионные всех продавцов которые были назначены по крайней мере двум заказчикам:

UPDATE Salespeople

SET comm=comm + .01

WHERE 2 <=

( SELECT COUNT (cnum)

FROM Customers

WHERE Customers.snum=

Salespeople.snum );

Теперь продавцы Peel и Serres, имеющие многочисленых заказчиков, получат повышение своих комиссионных.

Имеется разновидность последнего примера из предыдущего раздела с DELETE. Он уменьшает комиссионные продавцов которые произвели наименьшие порядки, но не стирает их в таблице:

UPDATE Salespeople

SET comm=comm - .01

WHERE snum IN

( SELECT snum

FROM Orders a

WHERE amt=

( SELECT MIN (amt)

FROM Orders b

WHERE a.odate=b.odate ));

СТОЛКНОВЕНИЕ С ОГРАНИЧЕНИЯМИ ПОДЗАПРОСОВ КОМАНДЫ DML

Неспособность сослаться к таблице задействованой в любом подзапросе из команды модификации (UPDATE), устраняет целые категории возможных действий. Например, вы не можете просто выполнить такую операцию как удаление всех заказчиков с оценками ниже средней. Вероятно лучше всего вы могли бы сначала (Шаг 1.), выполнить запрос, получающий среднюю величину, а затем (Шаг 2.), удалить все строки с оценкой ниже этой величины:

Шаг 1.

SELECT AVG (rating)

FROM Customers;

Вывод=200.

Шаг 2.

DELETE

FROM Customers

WHERE rating < 200;

РЕЗЮМЕ