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

Важный принцип, который надо соблюдать при работе с командами модификации: нельзя в предложении 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.

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

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

DELETE FROM Customers WHERE snum = ANY (SELECT snum FROM Salespeople WHERE city = 'London');

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

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

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

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

ИСПОЛЬЗОВАНИЕ ПОДЗАПРОСОВ С 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.odat = b.odate));

 

В начало