Лекция 20: Расширенное описание T-SQL

Оператор UPDATE

Оператор UPDATE используется для модифицирования или обновления существующих данных. Ниже показан синтаксис оператора UPDATE:

UPDATE имя_таблицы SET имя_колонки = выражение
   [FROM источник_для_таблицы] WHERE условие_поиска

Модифицирование строк

Основываясь на таблице items, мы обновим сначала строку junk food, которую вставили раньше без указания цены (колонка price). Чтобы найти строку, задайте в условии поиска текст fried pork skins. Чтобы задать (заменить) цену на $2, используйте следующий оператор:

UPDATE items SET price = 2.00
WHERE item_desc = 'fried pork skins'
GO
Теперь выберите строку junk food с помощью следующего запроса:
SELECT * FROM items
WHERE item_desc = 'fried pork skins'
GO

Результат для строки junk food появится в следующем виде, причем исходное значение NULL колонки price будет заменено на 2.00:

item_category     item_id   price       item_desc
-------------------------------------------------------------
junk food         2           2.00        fried pork skins

Чтобы увеличить значение этого элемента на 10 процентов, вы можете запустить следующий оператор:

UPDATE items SET price = price * 1.10
WHERE item_desc = 'fried pork skins'
GO

Теперь, выбрав строку junk food, вы увидите, что цена изменилась до $2.20 (значение $2, умноженное на 1.10). Цены других элементов не изменились.

С помощью оператора UPDATE вы можете модифицировать более чем одну строку. Например, чтобы модифицировать все строки в таблице items, увеличив все значения колонки price на 10 процентов, запустите следующий оператор:

UPDATE items SET price = price * 1.10
GO

Теперь в случае проверки таблицы items она будет выглядеть следующим образом:

item_category     item_id   price       item_desc
-------------------------------------------------------
health food       1         4.40        tofu 6 oz.
junk food         2         2.42        fried pork skins
toys              3         NULL        No desc

Строки со значением NULL в колонке price не затрагиваются, поскольку NULL * 1.10 = NULL. Это не является какой-либо проблемой, и вы не получите сообщения об ошибке.

Использование предложения FROM

Оператор UPDATE позволяет вам использовать предложение FROM для указания таблицы, которая будет использоваться как источник данных при модифицировании. В список источников таблиц могут включаться имена таблиц, имена представлений, функции rowset, производные таблицы и связанные таблицы. Источником может быть даже таблица, находящаяся в процессе модифицирования. Чтобы понять, как действует этот процесс, создадим еще одну небольшую таблицу. Ниже показаны оператор CREATE TABLE для нашей новой таблицы с именем tax и оператор INSERT для вставки строки со значением 5.25 в колонку tax_percent (процент налогообложения):

CREATE TABLE tax
(
tax_percent        real                    NOT NULL,
change_date        smalldatetime       DEFAULT getdate()
)
GO
INSERT INTO tax
    (tax_percent) VALUES (5.25)
GO

В колонку change_date (дата изменения) будут помещены текущие дата и время, полученные из ее используемой по умолчанию функции GETDATE, поскольку дата не была задана явным образом.

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

ALTER TABLE items
ADD price_with_tax smallmoney NULL
GO

Затем нам нужно модифицировать новую колонку price_with_tax, чтобы она содержала результат операции items.price * tax.tax_percent для всех строк таблицы items. Для этого используйте следующий оператор UPDATE с предложением FROM:

UPDATE items
SET price_with_tax = i.price +  
    (i.price * t.tax_percent / 100) 
FROM items i, tax t 
GO

Этот оператор UPDATE реально подходит как триггер, который будет запускаться при вставке значения в колонку price. Триггер – это специальный тип хранимой процедуры, которая автоматически выполняется при возникновении определенных условий. (О триггерах см. "лекцию 22" .)

Использование производных таблиц

Еще одним способом использования оператора UPDATE является применение производной таблицы (или подзапроса) в предложении FROM. Производная таблица используется как входной параметр для внешнего оператора UPDATE. Для этого примера мы будем использовать в данном подзапросе таблицу two_newest_items table и таблицу items во внешнем операторе UPDATE. Нам нужно модифицировать две последние строки в таблице items, чтобы они содержали в колонке price_with_tax значение NULL. Выполняя запрос в таблице two_newest_items, мы можем найти значения item_id для строк, которые требуется модифицировать в таблице items. Это осуществляется с помощью следующего оператора:

UPDATE items
SET price_with_tax = NULL
FROM (SELECT item_id FROM two_newest_items) AS t1
WHERE items.item_id = t1.item_id
GO

Оператор SELECT применяется как подзапрос, результаты которого помещаются во временную производную таблицу с именем t1, которая затем используется в условии поиска (предложение WHERE ). В результате подзапроса мы получаем значения item_id 2 и 3. Таким образом, затрагиваются две строки таблицы items со значениями 2 или 3. Строка со значением item_id, равным 3, уже имеет значение NULL в колонке price_with_tax, поэтому ее значения не изменяются. А в строке со значением item_id, равным 3 vv, значение price_with_tax действительно изменяется на NULL. Набор результатов, где показаны все строки таблицы items, выглядит после этой модификации следующим образом:

item_category     item_id   price       item_desc          price_with_tax
--------------------------------------------------------------------------------
health food         1       4.40        tofu 6 oz.         4.6310
junk food           2       2.42        fried pork skins   NULL 
toys                3       NULL        No desc            NULL

Дополнительная информация. Для получения подробной информации о дополнительных параметрах, которые можно использовать с оператором UPDATE, таких как подсказки для таблиц и запросов, найдите в Books Online "UPDATE" и выберите подтему "Described".