Использование вложенных запросов
Операторы 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.