Язык запросов SQL

Операторы UPDATE DELETE и INSERT



Операторы UPDATE, DELETE и INSERT

Кроме операторов SELECT, предложения WHERE могут быть и в операторах UPDATE, DELETE и INSERT. А в этих предложениях, в свою очередь, могут быть такие же подзапросы, как и в предложениях WHERE, используемых в операторе SELECT.

Например, Zetec только что заключила с Olympic Sales соглашение о партнерстве, согласно которому Zetec "задним числом" предоставляет Olympic Sales десятипроцентную скидку на весь прошлый месяц. Информацию об этой скидке можно ввести в базу данных, используя оператор UPDATE:

UPDATE TRANSMASTER

   SET NET_AMOUNT = NET_AMOUNT * 0.9

   WHERE CUSTID =

      (SELECT CUSTID

         FROM CUSTOMER

         WHERE COMPANY = 'Olympic Sales')

В операторе UPDATE можно также использовать и коррелированный подзапрос. Предположим, что в таблице CUSTOMER имеется столбец LAST_MONTHS_MAX (максимум за последние месяцы), а руководство Zetec хочет предоставить скидку для всех сделок, которые превышают значение LAST_MONTHS_MAX данного клиента:

UPDATE TRANSMASTER ТМ

    SET NET__AMOUNT = NET_AMOUNT * 0.9

    WHERE NET_AMOUNT >



         (SELECT LAST_MONTHS_MAX

                  FROM CUSTOMER С

                  WHERE C.CUSTID = TM.CUSTID) ;

Обратите внимание, что этот подзапрос является коррелированным. Дело в том, что предложение WHERE, расположенное в последней строке оператора, обращается одновременно и к значению CUSTID из строки, полученной с помощью подзапроса из таблицы CUSTOMER, и к значению CUSTID из текущей строки-кандидата на обновление, которая находится в таблице TRANSMASTER.

Подзапрос в операторе UPDATE может обращаться и к обновляемой таблице. Предположим, что руководство Zetec хочет дать десятипроцентную скидку покупателям, купившим товаров на сумму более 10 000 долларов:

UPDATE TRANSMASTER TM1

   SET NET_AMOUNT = NET_AMOUNT * 0.9

   WHERE 10000 < (SELECT SUM(NET_AMOUNT)

     FROM TRANSMASTER TM2

         WHERE TM1.CUSTID = TM2.CUSTID);

Во внутреннем подзапросе для всех строк таблицы TRANSMASTER, которые относятся к одному и тому же покупателю, вычисляется (с помощью функции SUM) сумма значений из столбца NET_AMOUNT. Что это означает? Предположим, что в таблице TRANSMASTER к покупателю со значением CUSTID, равным 37, относятся четыре строки, в которых столбец NET_ AMOUNT имеет такие значения: 3000, 5000, 2000 и 1000. Для этого значения CUSTID сумма значений NET_AMOUNT равна 11000.

Обратите внимание, что порядок, в котором оператор UPDATE обрабатывает строки, определяется конкретной реализацией и обычно является непредсказуемым. Этот порядок может зависеть от того, каким образом строки хранятся на диске. Предположим, что в имеющейся реализации для значения столбца CUSTID, равного 37, строки таблицы TRANSMASTER обрабатываются в следующем порядке. Первой — строка со значением NET_AMOUNT, равным 3000, затем — с NET_AMOUNT, равным 5000, и т.д. После обновления первых трех строк со значением CUSTID, равным 37, у них в столбце NET_AMOUNT будут такие значения: 2700 (90% от 3000), 4500 (90% от 5000) и 1800 (90% от 2000). А затем, когда в TRANSMASTER идет обработка последней строки, в которой значение CUSTID равно 37, a NET_AMOUNT равно 1000, то значение функции SUM, возвращенное подзапросом, должно быть равно 10000. Это значение получается как сумма новых значений NET_AMOUNT из первых трех строк со значением CUSTID, равным 37, а также старого значения из последней строки, имеющей то же значение CUSTID. Таким образом, может показаться, что последняя строка для значния CUSTID, равного 37, не должна обновляться — ведь сравнение с этим значением SUM не будет истинным (10000 не меньше SELECT SUM(NET_AMOUNT)). Но при обращении подзапроса к обновляемой таблице оператор UPDATE работает уже по-другому. В этом операторе при всех проверках подзапросов используются старые значения обновляемой таблицы. В предыдущем операторе UPDATE для столбца CUSTID, равного 37, подзапрос возвращает 11000, т.е. первоначальное значение SUM.

Подзапрос в предложении WHERE работает точно так же, как оператор SELECT или UPDATE. To же самое верно для DELETE или INSERT. Чтобы удалить записи обо всех сделках Olympic Sales, используйте такой оператор:

DELETE TRANSMASTER

    WHERE CUSTID =

         (SELECT CUSTID

                  FROM CUSTOMER

                  WHERE COMPANY = 'Olympic Sales') ;

Как и в случае с UPDATE, подзапросы DELETE также могут быть коррелированными и также могут обращаться к изменяемой таблице (у которой в данном случае удаляют строки). Здесь действуют правила, похожие на те, что используются для подзапросов оператора UPDATE. Предположим, вы хотите удалить из таблицы CUSTOMER все строки тех пользователей, для которых итог NET_AMOUNT больше 10000 долларов:

DELETE TRANSMASTER TM1

  WHERE 10000 < (SELECT SUM(NET_AMOUNT))

     FROM TRANSMASTER TM2

         WHERE TM1.CUSTID = TM2.CUSTID) ;

Этот запрос удаляет из таблицы TRANSMASTER все строки, в которых столбец CUSTID содержит 37, а также строки, относящиеся к другим пользователям, сумма покупок которых превышает 10000 долларов. Все обращения к TRANSMASTER, имеющиеся в подзапросе, указывают на содержимое этой таблицы, которое было перед любыми удалениями, уже выполненными текущим оператором. Поэтому даже при удалении из таблицы TRANSMASTER последней строки, в которой значение столбца CUSTID равно 37, подзапрос все равно выполняется на этой таблице таким образом, как если бы не было никаких удалений. В итоге подзапрос возвращает значение 11000.

При обновлении, удалении или вставке записей базы данных есть риск сделать так, что данные в изменяемой таблице не будут соответствовать данным в других таблицах из этой базы. Такое несоответствие называется аномалией изменения (см. главу 5). Если из таблицы TRANSMASTER удаляются записи, а от нее зависит другая таблица, TRANSDETAIL (подробности сделок), то записи, соответствующие удаленным записям из первой таблицы, необходимо удалять и из второй. Эта операция называется каскадным удалением, поскольку удаление родительской записи должно вызывать каскад удалений связанных с ней дочерних записей. В противном случае неудаленные дочерние записи становятся "записями-призраками".

В операторе INSERT может находиться предложение SELECT. Такие операторы применяются для заполнения таблиц с текущей информацией. Ниже приведен запрос для создания таблицы с содержимым TRANSMASTER за 27 октября.

CREATE TABLE TRANSMASTER_1027

         (TRANSID INTEGER, TRANSDATE DATE,

         . . . ) ;

INSERT INTO TRANSMASTER_1027

         (SELECT * FROM TRANSMASTER

                  WHERE TRANSDATE = 2000-10-27) ;

Если требуется информация лишь о крупных сделках, то запрос будет таким:

INSERT INTO TRANSMASTER_102 7

         (SELECT * FROM TRANSMASTER TM

         WHERE TM.NET_AMOUNT > 10000

                  AND TRANSDATE 2000-10-27) ;

 


Содержание раздела