Материал: Graber_Martin_-_SQL_dlya_prostykh_smertnykh_-_2014

Глава 16. Использование подзапросов с командами обновления

SELECT •

FROM Salespeople outer WHERE city IN

(SELECT city

FROM Customers inner

WHERE inner.snum = outer.snum);

Ни таблицу Salespeople, ни таблицу Samecity нельзя использовать во внешних или внутренних запросах для INSERT. Другой пример: предположим, назначено вознагра­

ждение для каждого продавца, имеющего максимальный заказ на каждый день. Сведе­

ния о них можно формировать в таблице Bonus, содержащей значение поля snum для продавца, дату (odate) и объем заказа (amount, amt). Эту таблицу можно заполнить ин­ формацией, хранящейся в таблице Orders, используя следующую команду:

INSERT INTO Bonus

SELECT snum, odate, amt FROM Orders а

WHERE amt =

(SELECT МАХ (amt) FROM О rde rs Ь

WHERE a.odate = b.odate);

Несмотря на то, что команда имеет подзапрос, базирующийся на той же таблице, что и внешний запрос, она не ссылается на таблицу Bonus, на которую воздействует эта ко­ манда. Следовательно, ее можно использовать. Логика запроса заключается в просмот­ ре таблицы Orders, и д.1я каждой строки осуществляется поиск максимальной заявки для конкретной даты. Если ее величина совпадает со значением поля amt текущей стро­ ки, то эта текущая строка и интересна, и ее данные заносятся в таблицу Bonus.

Использование подзапросов с DELETE

В предикате команды DELETE можно использовать подзапросы. Это дает возмож­ ность формулировать достаточно сложные критерии удаления строк, что требует осо­ бой внимательности. Например, если необходимо закрыть лондонский офис, можно

использовать следующий запрос для исключения всех покупателей, назначенных про­

давцам в London:

DELEТE

FROM Customers WHERE snum = ANY

(SELECT snum

FROM Salespeople

170

Ис11ользова11ие 11одза11росов с DELETE

WHERE city = 'London');

В соответствии с этой командой из таблицы Customers будут исключены строки Hoffman, Clemens (обе назначены Рее\) и строка Periera (назначенная Motika). Жела­ тельно удостовериться в верном выполнении этой операции, прежде чем реально уда­ лить или изменить строки с Рее! и Motika.

Внимание! Когда выполняется изменение в базе данных, которое влечет друrие изме­

нения, первое же.1ание - это выпо:~нить сначала основное изменение, а затем - трасси­

ровку тех изменений, которые последуют в связи с первым. Этот пример показывает, почему более эффективно выполнять работу в обратной последовательности, т.е. сначала осуществить вторичные изменения. Если изменение значения поля city началось с вьщачи новых значений (назначений) продавцам, то выполнение трассировки всех их покупате­ лей окажется делом более сложным. Поскольку реальные базы данных существенно пре­ восходят простые таблицы, которые рассматриваются здесь в качестве примера, при работе с ними могут возникнуть серьезные проблемы. Вам может оказаться полезен меха­ низм ссьшочной целостности SQL, но он не всегда применим и доступен.

Хотя нельзя сослаться в предложении FROM (подзапроса) на таблицу, из которой

осуществляется удаление, в предикате можно ссылаться на текущую строку-кандидат

из таблицы, т.е. на ту строку, которая в настоящее время проверяется в основном пре­ дикате. Другими словами, можно использовать связанные подзапросы. Они от"1ичают­ ся от применяемых с INSERT тем, что действительно базируются на строках­

кандидатах из таблицы, на которую воздействует команда, а не на запросе для некото­

рой другой таблицы.

DELETE FROM Salespeople

WHERE EXISTS

(SELECT "

FROM Customers WHERE rating = 100

AND Salespeople.snum = Customers.snum);

Часть AND предиката внутреннего запроса ссылается на таблицу Salespeople. Это оз­ начает, что целый подзапрос будет выполняться отдельно для каждой строки таблицы Salespeople, как и в случае друrих связанных подзапросов. Команда удаляет всех продав­ цов, имеющих по крайней мере одного покупателя с рейтингом 100, из таблицы Salespeople. Для достижения этого существуют и другие способы. Приведем один из них:

DELEТE FROM Salespeople

WHERE 100 IN

(SELECT rating

FROM Customers

WHERE Salespeople.snum = Customers.snum);

Запрос находит все рейтинги покупателей каждого продавца и удаляет продавца,

имеющего покупателя с рейтингом 100.

171

Глава 16. Использоваиие 11одзапросов с кома11дами об11овле11ия

Можно применять также обычные связанные подзапросы, т.е. связанные с табли­ цей, на которую есть ссылка во внешнем запросе (а не в само\'! предложении DELETE). Например, можно найти наименьший заказ за каждый день и удалить про­ давца, которому такой заказ был адресован, с помощью следующей команды:

DELEТE FROM Salespeople

WHERE snum IN

(SELECT snum

FROM Orders а WHERE amt =

(SELECT MIN (amt) FROM Orders Ь

WHERE a.odate = b.odate));

Подзапрос в предикате DELETE использует связанный подзапрос. Этот внутренний запрос находит минимальный заказ на каждую дату для каждой строки внешнего запро­

са. Если его величина совпадает с величиной заказа текущей строки, предикат внешнего

запроса принимает значение "истина". Это значит, что текущая строка содержит мини­

мальную заявку на данную дату. Поле snum для продавца, ответственного за эту заявку,

извлекается и подставляется в основной предикат самой команды DELETE, которая за­ тем удаляет все строки с этим значением поля snum из таблиць1 Salespeople. (Поскольку snum - это первичный ключ таблицы Salespeople, найдется единственная строка, под­ лежащая удалению в соответствии с этим запросом. Однако, если окажется, что таких строк больше, все они будут удалены.) Таким образом в данном случае будут удалены строки со следующими значениями snum: 1007 - минимум за 3 октября 1990 г.; 1002 - минимум за 4 октября 1990 г.; 1001 - минимальная и единственная заявка за 5 октября 1990 г. (команда кажется особенно жесткой, потому что удаляет Рее\, как един­ ственную заявку за 5 октября 1990 г., но она хорошо иллюстрирует суть дела).

Для того чтобы сохранить Рее!, необходимо добавить другой подзапрос, как в сле­

дующем примере:

DELETE FROM Salespeople

WHERE snum IN

(SELECT snum FROM Orders а WHERE amt =

(SELECT MIN (amt)

FROM Orders Ь

WHERE a.odate b.odate)

AND 1 <

(SELECT COUNT (onum)

172

Использование подзапросов с VPDATE

FROM О rde rs Ь

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);

Теперь для продавцов Рее! и Serres, имеющих множество покупателей, будут уве­

личены комиссионные.

Приведем модификацию последнего примера для раздела, в котором рассматрива­ лась команда DELETE. Здесь уменьшаются комиссионные для продавцов, получив­

ших минимальные заказы:

UPDATE Salespeople

SET comm = comm - .01

WHERE snum IN

(SELECT snum

FROM Orders а

WHERE amt =

(SELECT MIN (amt)

FROM Orders Ь

WHERE a.odate = b.odate));

173

Глава 16. Использоваиие подза11росов с командами обновления

Ограничения подзапросов в командах DML

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

Шаг 1.

SELECT AVG (rating)

FROM Customers;

Выходными данными для этого запроса является значение 200.

Шаг2.

DELETE

FROM Customers

WHERE rating < 200;

Итоги

Теперь вам известны три команды, управляющие всем содержимым базы данных. Хотелось бы только пояснить несколько основных моментов, связанных с вводом и удалением значений из таблицы: когда эти команды следует выполнять данному поль­ зователю для данной таблицы и когда сделанные изменения станут постоянными.

Итак, команда INSERT применяется для того, чтобы добавить строки в таблицу. Можно ввести имена значений для строк в предложении VALUES (оно определяет только одну добавляемую строку), либо значения можно получить из запроса (это оз­ начает, что любое количество строк может быть добавлено с помощью одной коман­ ды). Если используется запрос, то он не может ссылаться на таблицу, в которую осуществляется вставка каким-либо из способов: либо с помощью предложения FROM, либо путем внешней ссылки (как это делается в связанных запросах). Это при­ менимо к любым подзапросам внутри данного запроса. Запрос сохраняет свободу в

применении связанных подзапросов или подзапросов, использующих имена таблиц в

предложении FROM внешних запросов (это яв;тяется общим случаем применения за­

просов).

DELETE и UPDATE используются для исключения строк из таблицы и изменения значений в них. Обе команды применяются ко всем строкам таблицы, но можно упот­ ребить и предикат для определения подмножества удаляемых или обновляемых строк. Этот предикат может содержать подзапросы, связанные с таблицей, из которой выпол-

174