Правильная ссылка на эту страницу
http://az-design.ru/Support/DataBase/SQL/CelkoJ/01h009.shtml

Глава 9. Операции с таблицами

       Строка в таблице SQL позволяет выполнить только три действия: вставить ее в таблицу, удалить оттуда или обновить содержащиеся в ней значения.

9.1. Оператор DELETE FROM

       Оператор DELETE FROM языка SQL удаляет из одной таблицы нуль или больше строк. В большинстве, хотя и не во всех интерактивных средствах SQL пользователь получает сообщение о том, сколько строк затрагивается операцией удаления, стандарт SQL-92 требует, чтобы процессор БД выводил информацию об удалении нуля строк. В SQL есть две формы оператора DELETE FROM: позиционированная (positioned) и поисковая (searched). Позиционированное удаление осуществляется с помощью курсоров; в поисковом удалении используется предложение WHERE, как в операторе SELECT.

9.1.1. Предложение DELETE FROM

       Ниже представлен синтаксис поискового удаления:

<delete statement searched> ::= 
   DELETE FROM <table name>
   [WHERE <search condition>]

       В предложении DELETE FROM указывается имя обновляемой таблицы или изменяемого представления. Обратите внимание, что в предложении DELETE FROM не допускается коррелированного имени; это позволяет избежать возможных ссылок на себя. В данной главе предполагается, что осуществляющий удаление пользователь получил привилегии DELETE для таблицы. При позиционированном удалении из базовой таблицы удаляется строка, являющаяся источником текущей строки курсора. Это означает, что курсор должен быть построен на запросе из базовой таблицы. Синтаксис SQL-92 такого удаления:

<delete statement positioned> ::= DELETE FROM <table name> WHERE CURRENT OF <cursor name>

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

9.1.2. Предложение WHERE

       Основная особенность предложения WHERE заключается в том, что оно необязательно. Если его не указать, будут удалены все строки таблицы. Большая часть (но не все) интерактивных продуктов SQL выводит пользователю предупреждение о том, что он собирается сделать, и просят подтверждения. Чтобы восстановить таблицу, сразу осуществите откат; если вы завершите транзакцию или настроите свой продукт SQL на ее автоматическое завершение, с большой вероятностью данные будут потеряны. Восстановить их сможет только администратор (однако, изучая SQL, вам, вероятно, придется пройти через все это по крайней мере один раз). Большинство реализаций SQL удаляет таблицу за два прохода. При первом прохождении помечаются все подлежащие удалению строки, соответствующие критерию предложения WHERE. Они удаляются за второе прохождение — либо немедленно, либо помечаются особой процедурой для удаления впоследствии. Важно отметить: когда строки помечены, таблица по-прежнему остается доступной для применения условия WHERE. В большинстве случаев метод удаления за два прохождения дает такой же результат, что и за одно. Как правило, предложение WHERE является простым предикатом, ссылающимся на константы или связи между столбцами строки. Например, удалить сотрудников из таблицы можно следующим способом:

DELETE FROM Personnel
 WHERE iq <= 100,  -- использование константы в простом предикате

       Или так:

DELETE FROM Personnel
WHERE hat_size = iq; -- использование столбцов в той же строке

       Хороший оптимизатор определит, что эти предикаты не зависят от таблицы в целом, и удалит строки за одно прохождение. Удаление в два прохождения отличается от данного, если ссылки производятся из самой таблицы. Давайте уволим сотрудников, коэффициент интеллекта которых ниже среднего для их отделов.

DELETE FROM Personnel
 WHERE iq < (SELECT AVG(iq)
               FROM Personnel AS P1
              WHERE Personnel.dept = Pl.dept); 

       Мы получим следующий результат:

Personnel
Emp        dept     iq     .
'Able'     'Acct'   101
'Baker'    'Acct'   105
'Charles'  'Acct'   106
'Henry'    'Mkt'    101
'Celko'    'Mkt'    170
'Popkin'   'HR'     120

       Если проводить эту операцию по одной строке за раз, нам сначала пришлось бы обратиться к таблице Accounting, найти в нем средний IQ (101 + 105 + 106)/3.0 = 104, после чего уволить сотрудника Able. Затем мы последовательно опускаемся вниз по таблице, снова находим средний IQ, равный уже (105 + 10б)/2.0 = 105.5, и увольняем сотрудника Baker. Избежать этой участи сможет только Charles.
       Попробуем отсортировать таблицу иначе, расположив строки в обратном алфавитном порядке. Сначала читаем IQ сотрудника Charles, определяем среднее значение IQ (101 + 105 + 106)/3.0 = 104 и сохраняем Чарльза в отделе. Поскольку теперь IQ не изменился, мы сохраняем также и Бэйкера. Эйбла тем не менее предстоит уволить. Следует упомянуть, что в DB2 строки удаляются в том порядке, в котором они содержатся на диске. Реализация WATCOM SQL предусматривает необязательное предложение ORDER BY, сортирующее таблицу и осуществляющее в ней последовательное удаление только после этого. Если порядок не имеет значения, можно вызвать последовательное удаление, сэкономив на втором прохождении. Кроме того, так можно получить желаемый результат и в тех случаях, когда иначе нельзя было бы обойтись без курсора и использования базового языка.
       Андерс Альберг, Джоанна Вечер и я протестировали несколько версий оператора DELETE, попытавшись удалить все строки группы, кроме одной (см. раздел 9.1.4, посвященный описанию работы с идентификатором ROWID). Это три оператора:

D1:
   DELETE FROM Test 
    WHERE EXISTS (SELECT T1.id
                    FROM Test AS T1
                   WHERE T1.id = Test.id
                     AND T1.dup_cnt < dup_cnt)
D2:
   DELETE FROM Test
    WHERE dup_cnt > (SELECT MIN(T1.dup_cnt)
                       FROM Test AS T1
                      WHERE T1.id - Test.id);
D3:
   BEGIN ATOMIC
   INSERT INTO WorkingTable(id,mm_dup_cnt)
   SELECT id, MIN(dupcnt)
     FROM Test
    GROUP BY id;
   DELETE FROM Test
    WHERE dupcnt > (SELECT mm_dup_cnt
                      FROM WorkingTable
                     WHERE Working.id = Test.id); END;

       Скорости их работы в одном и том же продукте SQL для настольных систем были:

D1     3.20 с 
D2    31.22 с 
D3     0.17 с

       Я и не видел планов их выполнения, однако могу предположить, что оператор D1 обратился к индексу теста EXISTS() и вернул TRUE для первого найденного элемента. С другой стороны, D2 просканировал все подмножества в разделе таблицы Test по идентификатору, снова находя значение MIN(). Наконец, D3 просто сделал соединение нескольких скалярных столбцов.
       Для полной реализации стандарта SQL-92 оператор D3 можно было бы записать так:

D3-2:
   DELETE FROM Test 
    WHERE dupcnt > (SELECT min_dup_cnt
                      FROM (SELECT id, MIN(dupcnt)
                              FROM Test
                             GROUP BY id) AS WorkingTable(id, min_dup_cnt)
                     WHERE Working.id = Test.id);

9.1.3. Удаление, основанное на данных второй таблицы

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

DELETE FROM Deadbeats 
 WHERE EXISTS (SELECT *
                 FROM Payments AS P1 
                WHERE Deadbeats.custno = P1.custno 
                  AND P1.amtpaid >= Deadbeats.amtdue);

       К предложению WHERE оператора DELETE FROM применимы также все правила для области действия оператора SELECT, но лучше все-таки квалифицировать имена столбцов.

9.1.4. Удаление из той же самой таблицы

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

DELETE FROM Students 
 WHERE grade < (SELECT AVG(grade) FROM Students);

       Однако предложение DELETE FROM не допускает коррелированных имен для таблицы, поэтому не все предложения WHERE, которые можно было бы написать в составе оператора SELECT, будут работать в операторе DELETE FROM. Например, соединение таблицы с собой для рабочей таблицы в подзапросе невозможно:

DELETE FROM Employees AS B1 - неправильное коррелированное имя
 WHERE Employees.bossno = B1.empno 
   AND Employees.salary > B1.salary);

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

CREATE VIEW Bosses AS SELECT empno, salary FROM Employees; DELETE FROM Employees WHERE EXISTS (SELECT * FROM Bosses AS B1 WHERE Employees.bossno = B1.empno AND Employees.salary > B1.salary);

       Простое использование в подзапросе таблицы Employees работать не будет. В предложении WHERE подзапроса нам нужна внешняя ссылка на эту таблицу, и мы не можем получить ее, если в подзапросе используется данная таблица. Подобные представления должны быть настолько малы, насколько это возможно, чтобы процессор SQL мог бы разместить их в оперативной памяти.

Избыточные дубликаты в таблице
       Избыточные дубликаты представляют собой ненужные копии строки в таблице. Обычно они возникают, если на таблицу не было наложено ограничение UNIQUE, и одни и те же данные вводились в нее два раза. Удалить из таблицы SQL избыточные копии несколько сложнее, чем может показаться.
       Более того, если строки представляют собой точные дубликаты, вы не сможете удалить их простым оператором DELETE FROM. Вам надо сохранить одну из копий, удаляя другие. Если же нет способа различить их, будут удалены все строки, квалифицированные конструкцией WHERE. Кроме того, удаление строки из базовой таблицы может запустить действия по цепочке ссылок, что приведет к нежелательным побочным эффектам. Например, удаление избыточных дубликатов в таблице Table 1 может запустить удаление соответствующих строк таблицы Table2, что не всегда нуучо. В соответствии с правилом ссылочной целостности одной строке в Т1 должна соответствовать хотя бы одна строка в Т2. SQL-92 позволяет создавать ограничения с задержкой и без задержки, так что вы сможете отложить вызванное следующей транзакцией ссылочное действие:

BEGIN
INSERT INTO WorkingTable  -- уничтожение дубликатов с помощью DISTINCT
SELECT DISTINCT * FROM MessedUpTable;
DELETE FROM MessedUpTable; -- очистка смешанной таблицы
INSERT INTO MessedUpTable  -- ввод в нее данных из рабочей таблицы
SELECT * FROM WorkingTable;
DROP TABLE WorkingTable;   -- удаление рабочей таблицы 
END;

Удаление избыточных дубликатов с помощью идентификатора ROWID
       Интересные способы удаления ненужных повторов строк в БД Oracle предложил Леонард С. Медел. Рассмотрим такую таблицу:

CREATE TABLE Personnel
   (id     INTEGER NOT NULL,
    name   CHAR(30) NOT NULL . . .);

Классическое решение Oracle по удалению дубликатов представлено в следующем операторе:

DELETE FROM Personnel
 WHERE ROWID < (SELECT MAX(P1.ROWID)
                  FROM Personnel AS P1 
                 WHERE P1.id = Personnel.id 
                   AND P1.name = Personnel.name);
   AND  . . . );

       Столбец, или, точнее, псевдостолбец, ROWID заполняется на основании информации о физическом расположении строки в памяти. Его можно изменить после завершения сеанса пользователя, но не во время этого сеанса. Использование ROWID — это самый быстрый способ физического обращения к таблице Oracle, так как обращение производится непосредственно по физическому адресу данных.
       Воспользовавшись описанными далее двумя альтернативами, Медел добился приблизительно десятикратного повышения производительности.
       Первая возможность заключается в поиске самого большого значения ROWID для каждой группы из одной или более повторяющихся строк с последующим удалением всех этих строк, кроме соответствующей найденному значению.

DELETE FROM Personnel
 WHERE ROWID IN (SELECT P2.ROWID
                   FROM Personnel AS P2,
                        (SELECT P3.id, P3.name, ...
                                MAX(P3.ROWID) AS max_rowid
                           FROM Personnel AS P3
                          GROUP BY P3.id, P3.name, ...) AS P4
                  WHERE P2.ROWID <> P4.max_rowid
                    AND P2.id = P4.id 
                    AND P2.name = P4.name);

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

DELETE FROM Personnel
 WHERE ROWID IN (SELECT P2.ROWID
                   FROM Personnel AS P2
                 EXCEPT 
                 SELECT MAX(P3.ROWID)
                   FROM Personnel AS P3
                  GROUP BY P3.id, P3.name, . . . );

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

9.1.5. Удаление строк из нескольких таблиц без ограничений ссылочной целостности

       В одном операторе DELETE FROM невозможно удалить строки более чем из одной таблицы. Поэтому для удаления связанных строк из нескольких таблиц приходится прибегать к дополнительным приемам. Один из них связан с использованием временной таблицы с удаляемыми значениями, второй — с действиями по поддержке ссылочной целостности, как описано в стандарте SQL-92. Предположим, что наша БД содержит таблицы Orders и Inventory. В соответствии с бизнес-правилом при отсутствии какого-либо предмета на складе мы удаляем его из всех заказов.
       Допустим, что ограничений ссылочной целостности объявлено не было. В таком случае сначала надо создать временную таблицу из удаляемых продуктов на основе критерия поиска, а затем воспользоваться ею в коррелированном подзапросе и удалить строки из каждой участвующей в операции таблицы.

CREATE MODULE Foobar
CREATE LOCAL TEMPORARY TABLE Discontinue
    (partno INTEGER NOT NULL UNIQUE)
ON COMMIT DELETE ROWS;
...
PROCEDURE CleanInventory(...) 
BEGIN ATOMIC 
INSERT INTO Discontinue
SELECT DISTINCT partno -- выбор удаляемых элементов
  FROM ...
 WHERE ... ;  - использование вашего критерия 
DELETE FROM Orders
 WHERE partno IN (SELECT partno FROM Discontinue); 
DELETE FROM Inventory
 WHERE partno IN (SELECT partno FROM Discontinue);
COMMIT WORK;
END;
END MODULE;

       He все продукты SQL в настоящее время поддерживают средство SQL-92 CREATE LOCAL TEMPORARY TABLE. В схеме сохраняется временная таблица, но не ее содержание. Следовательно, в начале сеанса временные таблицы всегда бывают пустыми и принадлежат лишь одному пользователю. Параметр GLOBAL означает, что каждое приложение получает для всех своих модулей одну копию таблицы, a LOCAL ограничивает область действия модулем, в котором она была определена.

9.2. Оператор INSERT INTO

       Оператор INSERT INTO — это единственный способ ввода данных в базовую таблицу. На практике всегда существуют и другие средства для загрузки в таблицы больших объемов информации, но реализация этих способов зависит от конкретных разработчиков.

9.2.1. Предложение INSERT INTO

Синтаксис SQL-92 для INSERT INTO:

<msert statement> ::=
   INSERT INTO <table name>
      <msert columns and source>
<msert columns and source> ::=
   [(<msert column list>)] <query expression>
    VALUES <table value constructor list>
   | DEFAULT VALUES
<table value constructor list> ::=
   <row value constructor [{ <comma> <row value constructor } ...]
<row value constructor ::=
   <row value constructor element>
 | <left paren> <row value constructor list> <right paren>
 | <row subquery>
<row value constructor list> ::= 
   <row value constructor element>
      [{ <comma> <row value constructor element> }...]
<row value constructor element> ::=
   <value expression> 
 | <null specification> 
 | <default specification>
<null specification> ::= NULL 
<default specification> ::= DEFAULT

       Две основные формы оператора — это ввод табличной константы (обычно одной строки) и ввод запроса. Первый осуществляется с помощью предложения VALUES(). Список вводимых значений, как правило, содержит константы и явно выраженные значения NULL, но в принципе это может быть практически любое выражение SQL-92, включая скалярные подзапросы SELECT. До сих пор я не знаю ни одного продукта, который полностью поддерживал бы эту возможность.
       Новая конструкция SQL-92 DEFAULT VALUES является просто сокращенной формой записи выражения VALUES (DEFAULT, DEFAULT, DEFAULT).Она позволяет вставить в таблицу одну конкретную строку.
       Вставка табличной константы представляет собой простое средство, как правило, применяющееся в интерактивных сеансах для ввода небольшого количества информации. При вставке подзапроса выполняется запрос, создающий готовую таблицу, которая сразу вводится в целевую. В любом случае столбцы из необязательного списка столбцов целевой таблицы должны быть совместимы по объединению со столбцами запроса или со значениями предложения VALUES. Если столбца нет в списке, он получит либо значение NULL, либо заданное явным образом значение по умолчанию.

9.2.2. Природа вставок

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

INSERT INTO SomeTable (somekey, transactiontime)
SELECT millions, CURRENT_TIMESTAMP FROM HugeTable;

       в каждой строке результата за все время транзакции содержит только одно значение независимо от продолжительности их загрузки в таблицу Some-Table. Следовательно, необходимо большое количество проверок. Ввод не получится, если хотя бы одна строка нарушает наложенные на целевую таблицу ограничения. Обычная физическая реализация заключается в том, что размещение в целевой таблице строк должно быть помечено как незавершенное до проверки результата работы всей транзакции. После завершения ввода система должна перестроить все свои индексы. Последнее предполагает блокировку записей от других пользователей и может потребовать сортировки записей, если индекс в таблице уникальный или кластеризованный. Те, кто имел дело ранее с файловыми системами, могут попытаться просто удалить индексы, ввести новые данные, отсортировать таблицу и индексировать ее заново. Действительно, утилиты индексирования работают лучше, если таблица уже упорядочена. К сожалению, в SQL данный прием применим не всегда. Индексы поддерживают ограничения уникальности и ссылочной целостности и не могут быть легко удалены и восстановлены. Файлы существуют сами по себе, а таблицы — это части БД.

9.2.3. Утилиты массовой загрузки и выгрузки

       Все версии SQL содержат расширения или утилиты, позволяющие прочитать данные из внешнего файла непосредственно в таблицу. Для такого средства не определен стандарт, поэтому в каждой реализации они различны. Большинство из них требует указания имени и формата файла. Простейшая версия утилиты просто читает файл и вводит его в целевую таблицу. С другой стороны, компания Oracle использует простой язык, позволяющий осуществлять редактирование читаемых записей. Если вы используете более простое средство, то полезно создать рабочую таблицу, поместить в нее данные, очистить их и только потом ввести в настоящую целевую таблицу. В процессе очистки можно выполнить специальные процедуры редактирования, найти дубликаты и перенести неприемлемые данные в другую рабочую таблицу для более тщательного изучения. Соответствующая утилите массовой загрузки утилита вывода, которая конвертирует таблицу в файл, как правило, предлагает выбор средств форматирования; все подсчеты и выбор записей осуществляется средствами SQL. Некоторые программы принимают оператор SELECT или представление, другие просто конвертируют базовую таблицу. Большинство современных средств позволяет выводить операторы INSERT INTO, CREATE TABLE и CREATE INDEX.

9.3. Оператор UPDATE

       Назначение оператора UPDATE в языке SQL заключается в изменении значений в столбце либо в строке таблицы, либо в отсутствии такого изменения. Реализации SQL, как правило, сообщают, сколько строк было затронуто операцией обновления, или по крайней мере возвращают значения кодов SQLSTATE или SQLCODE, если ни одна из строк не оказалась затронутой. В SQL есть две формы оператора обновления — позиционное и поисковое. Позиционное (positioned) обновление осуществляется с помощью курсоров; в поисковом (searched) используется предложение WHERE, напоминающее условие поиска оператора SELECT. Позиционное обновление в этой книге не рассматривается по двум причинам. Во-первых, курсоры применяются в базовых языках программирования, а мы занимаемся здесь, по мере возможности, "чистым" языком SQL. Во-вторых, курсоры в стандарте SQL-92 отличаются от варианта SQL-89 и на момент написания книги полностью не поддерживались ни в одной реализации.

9.3.1. Предложение UPDATE

       Ниже представлен синтаксис поискового оператора UPDATE:

<update statement> ::=
   UPDATE <table name>
      SET <set clause list>
   [WHERE <search condition>]
<set clause list> ::=
   <set clause> [{ , <set clause> }...]
<set clause> ::== <object column> = <update source>
<update source> ::= <value expression | NULL | DEFAULT 
<object column> ::= <column name>

       Предложение UPDATE содержит имя обновляемой таблицы или изменяемого представления. Обратите внимание, что в ней не допускаются коррелированные имена; это обусловлено необходимостью избегать ссылок на себя. Предложение SET содержит список изменяемых или создаваемых столбцов; WHERE показывает, какие строки использовать. В данном примере мы предполагаем, что осуществляющий обновление пользователь имеет необходимые привилегии для каждого столбца <object column>.

9.3.2. Предложение WHERE

       Как уже упоминалось, предложение WHERE необязательно, это самое важное, что следует помнить о нем. Если его нет, обновляются все строки таблицы. Отсутствие такого предложения является распространенной ошибкой; сделав ее, немедленно выполните откат (команду ROLLBACK).

9.3.3. Предложение SET

       Каждый элемент списка <set clause list> выполняется одновременно, и каждое предложение SET одновременно изменяет все указанные строки. Во всяком случае, это теоретическая модель. На практике за первое прохождение указанные в предложении WHERE строки будут помечены. Если это удалось сделать без проблем (например, обнаружится, что критерию не соответствует ни одна строка), процессор SQL копирует помеченные строки в рабочую память. К копиям применяются команды предложения SET, а результаты записываются в виде нового образа строки. Наконец, новые строки заменяют старые. Если при этом возникает ошибка, изменения в таблице не происходят, а об ошибке выводится сообщение.
       Подобный параллелизм не похож на то, что встречается в традиционных языках третьего поколения, поэтому усвоить его непросто. Например, вы можете написать оператор, меняющий местами значения в двух столбцах:

UPDATE MyTable SET a = b, b = a;

       Этот оператор не совпадает со следующим:

BEGIN ATOMIC
UPDATE MyTable
   SET a = b;
UPDATE MyTable
   SET b = a;
END;

       В первом операторе значения столбцов а и b в каждой строке меняются местами. Во втором сначала столбцы а получают значения из столбцов b в каждой строке. Затем значения из столбцов а записываются снова в столбцы Ь, но, так как теперь эти значения совпадают, фактически изменения не произойдет. Допустимые выражения подчиняются определенным правилам. Один и тот же столбец не может появиться в списке <set clause list> более одного раза — это разумно с учетом параллельной природы оператора. Поскольку оба изменения наступают одновременно, вы не сможете определить, какое предложение SET будет использовано.

9.3.4. Обновления с помощью второй таблицы

       Так как обновления осуществляются посредством программ ввода данных, они выполняются в простых выражениях типа SET <column name> - <constant value> (в столбец вводится значение). Однако можно с обеих сторон знака равенства указать названия столбцов. При этом значения таблицы изменены не будут, но так можно запустить ссылочные действия с условием ON UPDATE. Список <set clause list> может содержать не только простые выражения. Можно, например, вывести итоговые данные одной таблицы в другую. Областью действия <table name> является весь оператор <update statements, так что на него можно сослаться в предложении WHERE. Рассмотрим следующие таблицы:

CREATE TABLE Customers
   (custno       INTEGER NOT NULL,
    acctbalance  DECIMAL(8,2) NOT NULL);
CREATE TABLE Payments
   (custno   INTEGER NOT NULL,
    transno  INTEGER NOT NULL,
    amt      DECIMAL(8,2) NOT NULL);

       Задача состоит в том, чтобы направить все счета на баланс в таблице Customers, переписав старый баланс. Обычно это выполняется пакетной операцией, так что логичным решением здесь представляется поисковый оператор UPDATE. Стандарт SQL-92 и некоторые — но не все — текущие реализации дают возможность включить имена обновляемых таблиц в подзапрос:

UPDATE Customers
   SET acctbalance = acctbalance
       - (SELECT SUM(amt)
            FROM Payments AS P1
           WHERE Customers custno = P1.custno)
 WHERE EXISTS (SELECT *
                 FROM Payments AS P2
                WHERE Customers custno = P2.custno);

       Если выплат не было, скалярный запрос возвращает пустое множество. Сумма SUM() для пустого множества всегда равна NULL. Очень распространенной ошибкой программирования при использовании данного приема является написание запроса, который может возвращать более одной строки. Так, последний пример мог бы иметь вид:

UPDATE Customers
   SET acctbalance = acctbalance 
       - (SELECT amt
            FROM Payments AS P1 
           WHERE Customers.custno = P1.custno) 
 WHERE EXISTS (SELECT *
  FROM Payments AS P2 
 WHERE Customers.custno = P2.custno);

       Допустим, однако, что покупатель осуществил более одной выплаты, и все они вошли в состав таблицы Payments; в таком случае транзакция не произойдет. Оператор UPDATE вернет сообщение об ошибке и будет произведен откат. Однако в первом примере мы гарантированно получаем скалярный результат, так как имеется только одно значение SUM (amt).
       Вторая распространенная связанная с оператором UPDATE ошибка программирования касается использования агрегатной функции, которая при применении к пустой таблице не возвращает нуля; например, это функция AVG (). Чтобы вывести среднюю выплату, а не их сумму, для всех покупателей, мы не можем просто заменить функцию SUM() на AVG(), а таблицу acctbalance на avgbalance. Нам придется включить в оператор UPDATE предложение WHERE, которое выбирает только заплативших пользователей.

UPDATE Customers
   SET payment = (SELECT AVG(P1.amt)
                    FROM Payments AS P1 
                   WHERE Customers.custno = P1.custno) 
 WHERE EXISTS (SELECT *
                 FROM Payments AS P1 
                WHERE Customers.custno = P1.custno);

       Предложение WHERE позволяет избежать NULL-значения и не допустить его распространения в вычислениях.

9.3.5. Выражение CASE в обновлениях

       Предусмотренное в стандарте SQL-92 выражение CASE очень удобно для обновления таблицы. Обратите внимание, что, написав выражение "SET a = а", вы не выполните в таблице никаких действий. Оператор можно переписать так:

UPDATE Customers
   SET payment = CASE WHEN EXISTS (SELECT *
                                     FROM Payments AS P1 
                                    WHERE Customers.custno = P1.custno) 
                      THEN (SELECT AVG(PLamt)
                              FROM Payments AS P1
                             WHERE Customers.custno = P1.custno) 
                      ELSE payment;

       Поскольку предложение WHERE отсутствует, оператор будет сканировать всю таблицу целиком. В данном случае это нежелательно; вероятно, в любой отдельный день только небольшое количество покупателей произведет выплату. Тем не менее в других ситуациях сканировать таблицу все-таки необходимо, и данная версия оператора сделает это быстрее.
       Однако истинное преимущество выражения CASE заключается в возможности объединить в один оператор несколько операторов UPDATE. Это сократит время выполнения и сэкономит вам процедурный код. Допустим, работая в реестре книг, вы хотите: 1) снизить на 15% цену всех книг дороже $25.00; 2) увеличить на 10% цену всех книг дешевле $25.00. Первая мысль — написать следующее:

BEGIN ATOMIC -- неверно
UPDATE Books
   SET price = price * 0.90
 WHERE price >= 25.00;
UPDATE Books
   SET price = price * 1.15
 WHERE price < 25.00;
END;

       Такой подход работать не будет. Например, если книга стоит ровно $25.00, она пройдет через первую часть обновления и будет стоить $22.50; затем она пройдет через вторую часть и будет стоить $25.88; но это не то, что нам требуется. Поменяв местами операторы, мы получим для этой книги желаемый результат, но, если книга стоит уже $24.95, мы получим $28.69, а затем $25.82.

UPDATE Books
   SET price = CASE WHEN price < 25.00
                    THEN price = price * 1.15 
                    ELSE price = price * 0.90
END;

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

BEGIN ATOMIC
UPDATE Foobar
   SET a = x
 WHERE r = 1;
UPDATE Foobar
   SET b = у
 WHERE s = 2;
UPDATE Foobar
   SET с = z
 WHERE t = 3;
UPDATE Foobar
   SET с = z + 1
 WHERE t = 4;
END;

Его можно заменить на приведенный ниже:

UPDATE Foobar
   SET a = CASE WHEN r = 1 THEN x ELSE a END,
       b = CASE WHEN s = 2 THEN у ELSE b END,
       с = CASE WHEN t = 3 THEN z 
                WHEN t = 4 THEN z + 1
                ELSE с END
 WHERE r = 1 OR s = 2 OR t IN (3, 4);

       Предложение WHERE необязательно, но может повысить производительность, если индекс составлен правильно и множество невелико. Обратите внимание, этот подход ориентирован на назначение оператора UPDATE — столбцы в предложении SET появляются только один раз. Традиционный подход ориентирован на источник изменений, данные обновляются сначала из одного источника, затем из другого и т.д. Подумайте, во что превратится подобный процесс, если с главным файлом связано несколько магнитных лент.

9.3.6. Обновления внутри одной таблицы

       Если выражение <object column> позволяет, то в качестве <update sourcе> можно указать NULL или значение DEFAULT. Однако используется значение выражения, представляющее собой константу или результат нескольких арифметических операций по отношению к тому же или другим столбцам той же строки. В соответствии с правилом предложение SET не должно непосредственно содержать спецификации <set function specification>. Это означает, что я не могу написать:

UPDATE Customers SET age = AVG(age); - недопустимый код SQL

       Однако в некоторых случаях удобно сослаться на таблицу в целом. Для этого приходится создавать представления или рабочие таблицы, что проиллюстрировано на следующем примере.
       В 1993 г. Рик Висик представил на форум SQL Forum статью "Advanced Transact SQL" (Vicik, 1993), в которой описал проблему поиска сотрудников, заработавших больше денег, чем их начальник, и снижения их зарплаты до уровня 90% от зарплаты последнего. В используемой для этого таблице сотрудников имеется столбец с их идентификационными номерами, empno, и столбец с идентификационными номерами их начальников, bossno. Вы не можете выполнить всю эту работу непосредственно, так как в большинстве версий SQL предложение SET оператора UPDATE не поддерживает коррелированных имен (за исключением старого продукта Transact SQL компании Sybase и SQLBase компании Centura). На языке SQLBase запрос можно записать так:

UPDATE Employees AS Boss - нестандартный код SQL!
   SET Employees.salary = (Boss.salary * 0.90)
 WHERE Employees.bossno = Boss.empno 
   AND Employees.salary >= Boss.salary;

       Другой метод будет работать и в стандартном варианте SQL. Сначала надо сконструировать рабочую таблицу или представление из сотрудников, которое выглядит как оригинальная таблица:

CREATE VIEW Adjustment (empno, ..., newsalary)
AS SELECT worker.empno,   ...,   (boss.salary * 0.90) 
     FROM Employees AS worker, Employees AS boss
    WHERE worker.bossno = boss.empno
      AND worker.salary >= boss.salary,

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

UPDATE Employees
   SET salary = (SELECT MAX(newsalary)
                   FROM Adjustment
                  WHERE Adjustment.empno = Employees.empno)
 WHERE empno IN (SELECT empno FROM Adjustment);

       Выражение МАХ(newsalary) гарантирует возврат подзапросом только одного значения. Оно показывает анализатору SQL, что здесь всегда будет только скалярное значение, так что правая сторона предложения SET всегда корректна. В некоторых реализациях SQL наличие нескольких строк в подзапросе проверяется только во время выполнения, а не компиляции, и функция МАХ() гарантирует переносимость кода.
       При отсутствии предложения WHERE в операторе UPDATE заработные платы для неучтенных здесь сотрудников будут установлены в NULL; это результат невозможности найти соответствующий номер empno в обеих таблицах.

9.3.7. Обновление первичного ключа

       Обновление первичного ключа не похоже на обновление неключевого столбца. По его завершении уникальность ключа должна быть сохранена. В ранних версиях DB2 и других реализациях SQL для ключевых и других столбцов с ограничением UNIQUE оператор UPDATE корректно не работал. Рассмотрим следующую таблицу:

CREATE TABLE MyList
   (seqno   INTEGER PRIMARY KEY,
    stuff1  CHAR(1) NOT NULL,
    stuffN  CHAR(1) NOT NULL);

       Здесь seqno — последовательный идентификационный номер. Задача — увеличить все номера существующих элементов на 1, чтобы к таблице можно было добавить новый элемент под номером 1. Для этого можно воспользоваться следующими двумя операторами:

BEGIN ATOMIC
UPDATE MyList SET seqno = seqno + 1;
INSERT INTO MyList VALUES (1,'a1',...,'aN' );
END;

       В некоторых ранних версиях SQL такой прием не работает, потому что в них делается попытка осуществлять обновление построчно внутри процессора, как при применении курсора, и проверять уникальность после обновления каждой строки. Если таблица отсортирована по возрастанию, сначала будет прочитана первая строка (seqno = 1), и номер увеличен на 1 (seqno = 2). Поскольку во второй строке уже имеется ключевое значение seqno = 2, обновление немедленно прекратится. Если же обращение к таблице осуществляется по убыванию, все работает нормально. Если таблица неупорядочена, сбой происходит случайным образом. Лучшим способом решения проблемы является преобразование последовательных номеров в отрицательные числа, их обновление и ввод новых значений.

BEGIN ATOMIC -- решение проблемы для нестандартных реализаций SQL 
UPDATE My List SET seqno = -(seqno +1); 
UPDATE MyList SET seqno = - (seqno),
INSERT INTO MyList VALUES (1, 'a1','aN');
END;

       Можно также удалить ограничения UNIQUE и PRIMARY KEY или индексы, осуществить обновление, ввести новую строку, после чего изменить ограничения с помощью оператора ALTER или воссоздать уникальный индекс с помощью оператора CREATE INDEX. Однако это занимает много времени, кроме того, вам придется блокировать БД от доступа к ней пользователей.

9.4. Недостатки распространенных расширений разработчиков

       В этой книге не затрагиваются нестандартные SQL-подобные языки, но в продукте T-SQL компании Sybase имеется столь серьезный недостаток, что о нем необходимо упомянуть. Операторы UPDATE и DELETE FROM здесь рассматриваются как отдельные запросы. Сначала в предложении FROM строится рабочая таблица (она добавлена к синтаксису), а затем с ее помощью осуществляется доступ к целевой БД, упомянутой в предложениях DELETE или UPDATE.
       Если в скрытом запросе строка базовой таблицы представлена несколько раз, она будет обработана также неоднократно, что является грубым нарушением реляционных принципов. Например:

CREATE TABLE T1 (х INTEGER NOT NULL);
INSERT INTO T1 VALUES (1), (2), (3), (4);
CREATE TABLE T2 (x INTEGER NOT NULL);
INSERT INTO T2 VALUES (1), (1), (1), (1);

       Попытайтесь теперь изменить таблицу Т1, продублировав строки, для которых в Т2 есть соответствие.

UPDATE T1
   SET Т1.х = 2 * Т1.х 
  FROM T2 
 WHERE T1.x = Т2.х;
Т1
x     .
16
2
3
4

       Предложение FROM дает вам перекрестное соединение (CROSS JOIN), так что к одной строке вы применяете последовательность действий (1 => 2 =>4 => 8 => 16). Теперь сделаем еще один шаг и включим вторую таблицу без отношений в ней вообще.

CREATE TABLE ТЗ (х CHAR(1) NOT NULL);
INSERT INTO ТЗ VALUES ('a'), ('a'), ('a'), ('a');
UPDATE T1
   SET T1.x = 2* T1.x 
  FROM T2, T3 
 WHERE T1.x = T2.x;
Tl
x     .
65536
2
3
4

       Как видите, это очень простые примеры, но они дают представление о происходящем. Многократное удаление одной строки с помощью оператора DELETE FROM не изменит результатов, но их многократное изменение приводит к катастрофическим последствиям. Некоторые аспекты проблемы были исправлены в последней версии Sybase, но синтаксис все еще не является ни стандартным, ни переносимым.




<<< Пред. Оглавление
 
След. >>>

Дата последнего изменения:
Thursday, 21-Aug-2014 09:10:55 MSK


Постоянный адрес статьи:
http://az-design.ru/Support/DataBase/SQL/CelkoJ/01h009.shtml