В ЭТОЙ ГЛАВЕ, ВЫ УЗНАЕТЕ КАК ИСПОЛЬЗОВАТЬ подзапросы в командах модификации.

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

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

Не делайте ссылки к текущей строке таблицы, указанной в команде, которая является соотнесенным подзапросом.

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

INSERT - это самый простой случай. Вы уже видели как вставлять результаты запроса в таблицу. Вы можете использовать подзапросы внутри любого запроса, который генерирует значения для команды INSERT тем же самым способом, которым вы делали это для других запросов - т.е. внутри предиката или предложения HAVING.

Предположим, что мы имеем таблицу с именем SJpeople, столбцы которой совпадают со столбцами нашей таблицы Продавцов. Вы уже видели как заполнять таблицу подобно этой, заказчиками в городе, например, в San Jose:

INSERT INTO Sjpeople

SELECT *

FROM Salespeople

WHERE city = San Jose;

Теперь мы можем использовать подзапрос чтобы добавить к таблице SJpeople всех продавцов которые имеют заказчиков в San Jose, независимо от того, находятся ли там продавцы или нет:

INSERT INTO Sjpeople SELECT *

FROM Salespeople

WHERE snum = ANY ( SELECT snum

FROM Customers

WHERE city = San Jose );

Оба запроса в этой команде функционируют также как если бы они не являлись частью выражения INSERT. Подзапрос находит все строки для заказчиков в San Jose и формирует набор значений snum. Внешний запрос выбирает строки из таблицы Salespeople, где эти значения snum найдены. В этом примере, строки для продавцов Rifkin и Serres, которые назначены заказчикам в San Jose - Liu и Cisneros, будут вставлены в таблицу SJpeople.

НЕ ВСТАВЛЯЙТЕ ДУБЛИКАТЫ СТРОК

Последовательность команд в предшествующем разделе может быть проблематичной. Продавец Serres находится в San Jose, и следовательно будет вставлен с помощью первой команды. Вторая команда попытается вставить его снова, поскольку он имеет еще одного заказчика в San Jose. Если имеются любые ограничения в таблице SJpeople, которые вынуждают ее значения быть уникальными, эта вторая вставка потерпит неудачу (как это и должно было быть). Двойные строки это плохо.(См. Главу 18 для подробностей об ограничениях.)

Было бы лучше если бы вы могли как-то выяснить, что эти значения уже были вставлены в таблицу, прежде чем вы попытаетесь сделать это снова, с помощью добавления другого подзапроса (использующего операторы типа EXISTS, IN, <> ALL, и так далее) к предикату.

К сожалению, чтобы сделать эту работу, вы должны будете сослаться на саму таблицу SJpeople в предложении FROM этого нового подзапроса, а, как мы говорили ранее, вы не можете ссылаться на таблицу которая задействована (целиком) влюбом подзапросе команды модификации.

Вслучае INSERT, это будет также препятствовать соотнесенным подзапросам, основанным на таблице в которую вы вставляете значения. Это имеет значение, потому что, с помощью INSERT, вы создаете новую строку в таблице. Текущая строка не будет существовать до тех пор, пока INSERT не закончит ее обрабатывать.

ИСПОЛЬЗОВАНИЕ ПОДЗАПРОСОВ, СОЗДАНЫХ ВО

ВНЕШНЕЙ ТАБЛИЦЕ ЗАПРОСА

Запрещение на ссылку к таблице, которая модифицируется командой INSERT, не предохранит вас от использования подзапросов, которые ссылаются к таблице, используемой в предложении FROM внешней команды SELECT. Таблица, из которой вы выбираете значения, чтобы произвести их для INSERT, не будет задействована командой; и вы сможете ссылаться к этой таблице любым способом которыми вы обычно это делали, но только если эта таблица указана в автономном запросе. Предположим что мы имеем таблицу с именем Samecity в которой мы запомним продавцов с заказчиками в их городах.

Мы можем заполнить таблицу используя соотнесенный подзапрос:

INSERT INTO (Samecity SELECT *

FROM (Salespeople outer

WHERE city IN ( SELECT city

FROM Customers inner

WHERE inner.snum = outer.snum );

Ни таблица Samecity, ни таблица Продавцов не должны быть использованы во внешних или внутренних запросах INSERT.

В качестве другого примера, предположим, что вы имеете премию для продавца который имеет самый большой порядок на каждый день. Выследитезанимвтаблице сименем Bonus, которая содержит поле snum продавцов, поле odate иполе amt. Вы должны заполнить эту таблицу информацией которая хранится в таблице Порядков, используя следующую команду:

INSERT INTO Bonus SELECT snum, odate, amt FROM Orders a

WHERE amt = ( SELECT MAX (amt) FROM Orders b

WHERE a.odate = b.odate );

Даже если эта команда имеет подзапрос, который базируется на той же самой таблице, что и внешний запрос, он не ссылается к таблице Bonus, на которую воздействует команда. Что для нас абсолютно приемлемо.

Логика запроса, естественно, должна просматривать таблицу Порядков, инахо-дить для каждой строки максимум порядка сумм приобретений для этой даты. Если эта величина - такая же как у текущей строки, текущая строка является наибольшим порядком для этой даты, и данные вставляются в таблицу Bonus.

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

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

Например, если мы закрыли наше ведомство в Лондоне, мы могли бы использовать следующий запрос, чтобы удалить всех заказчиков, назначенных к продавцам в Лондоне:

DELETE

FROM Customers

WHERE snum = ANY ( SELECT snum

FROM Salespeople

WHERE city = London );

Эта команда удалит из таблицы Заказчиков строки Hoffman и Clemens (назначенных для Peel), и Periera (назначенного к Motika).

Конечно, вы захотите удостовериться, правильно ли сформирована эта операция, прежде чем удалит или изменит строки Peel и Motika.

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

Если, например, вы решили изменить значение поля city ваших продавцов везде, где они переназначены, вы должны рассмотреть всех этих заказчиков более сложным способом.

Так как реальные базы данных имеют тенденцию развиваться до значительно больших размеров, чем наши небольшие типовые таблицы, это может стать серьезной проблемой.SQL может предоставить некоторую помощь в этой области, используя механизм справочной целостности (обсужденной в Главе 19), но этоневсегда доступно и не всегда применимо.

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