Справка по SQL

В результате выполнения этого оператора в таблицу Printer_Inc будет вставлена информация о модели 3111 цветного лазерного принтера, стоимость которого равна $2599. В поле code окажется значение, которое только случайно может оказаться равным 15. В большинстве случаев этого оказывается достаточно, т.к. значение автоинкрементируемого поля, как правило, не несет никакой информации; главное, чтобы оно было уникальным.

Однако бывают случаи, когда требуется подставить вполне конкретное значение в автоинкрементируемое поле. Например, нужно перенести уже имеющиеся данные во вновь создаваемую структуру; при этом эти данные участвуют в связи "один-ко-многим" со стороны "один". Таким образом, мы не можем допустить тут произвола. С другой стороны, нам не хочется отказываться от автоинкрементируемого поля, т.к. оно упростит обработку данных при последующей эксплуатации базы данных.

Поскольку стандарт языка SQL не предполагает наличия автоинкрементируемых полей, то соответственно не существует и единого подхода. Здесь мы покажем, как это реализуется в MS SQL Server. Оператор

отключает (значение ON) или включает (OFF) использование автоинкремента. Поэтому чтобы вставить строку со значением 15 в поле code, нужно написать

SET IDENTITY_INSERT Printer_Inc ON;

INSERT INTO Printer_Inc(code, model, color, type, price)

VALUES (15, 3111, 'y', 'laser', 2599);

Обратите внимание, что список столбцов в этом случае является обязательным, т.е. мы не можем написать так:

SET IDENTITY_INSERT Printer_Inc ON;

INSERT INTO Printer_Inc

VALUES (15, 3111, 'y', 'laser', 2599);

ни, тем более, так

SET IDENTITY_INSERT Printer_Inc ON;

INSERT INTO Printer_Inc(model, color, type, price)

VALUES (3111, 'y', 'laser', 2599);

В последнем случае в пропущенный столбец code значение не может быть подставлено автоматически, т.к. автоинкрементирование отключено.

Важно отметить, что если значение 15 окажется максимальным в столбце code,то далее нумерация продолжится со значения 16. Естественно, если включить автоинкрементирование: SET IDENTITY_INSERT Printer_Inc OFF.

Наконец, рассмотрим пример вставки данных из таблицы Product в таблицу Product_Inc, сохранив значения в поле code:

SET IDENTITY_INSERT Printer_Inc ON;

INSERT INTO Printer_Inc(code, model,color,type,price)

SELECT * FROM Printer;

По поводу автоинкрементируемых столбцов следует еще сказать следующее. Пусть последнее значение в поле code было равно 16, после чего строка с этим значением была удалена. Какое значение будет в этом столбце после вставки новой строки? Правильно, 17, т.к. последнее значение счетчика сохраняется, несмотря на удаление строки, его содержащей. Поэтому нумерация значений в результате удаления и добавления строк не будет последовательной. Это является еще одной причиной для вставки строки с заданным (пропущенным) значением в автоинкрементируемом столбце.

Оператор UPDATE

Оператор UPDATE изменяет имеющиеся данные в таблице. Команда имеет следующий синтаксис

UPDATE

SET {имя столбца = {выражение для вычисления значения столбца

| NULL

| DEFAULT},...}

[ {WHERE }];

С помощью одного оператора могут быть заданы значения для любого количества столбцов. Однако в одном и том же операторе UPDATE можно вносить изменения в каждый столбец указанной таблицы только один раз. При отсутствии предложения WHERE будут обновлены все строки таблицы.

Если столбец допускает NULL-значение, то его можно указать в явном виде. Кроме того, можно заменить имеющееся значение на значение по умолчанию (DEFAULT) для данного столбца.

Ссылка на "выражение" может относиться к текущим значениям в изменяемой таблице. Например, мы можем уменьшить все цены ПК-блокнотов на 10 процентов с помощью следующего оператора:

UPDATE Laptop SET price=price*0.9

Разрешается также значения одних столбцов присваивать другим столбцам. Пусть, например, требуется заменить жесткие диски менее 10 Гб в ПК-блокнотах. При этом емкость новых дисков должна составлять половину объема RAM, имеющейся в данных устройствах. Эту задачу можно решить следующим образом:

UPDATE Laptop SET hd=ram/2 WHERE hd<10

Естественно, типы данных столбцов hd и ram должны быть совместимы. Для приведения типов может использоваться выражение CAST.

Если требуется изменять данные в зависимости от содержимого некоторого столбца, можно воспользоваться выражением CASE. Если, скажем, нужно поставить жесткие диски объемом 20 Гб на ПК-блокноты с памятью менее 128 Мб и 40 гигабайтные - на остальные ПК-блокноты, то можно написать такой запрос:

UPDATE Laptop

SET hd = CASE WHEN ram<128 THEN 20 ELSE 40 END

Для вычисления значений столбцов допускается также использование подзапросов. Например, требуется укомплектовать все ПК-блокноты самыми быстрыми процессорами из имеющихся. Тогда можно написать:

UPDATE Laptop

SET speed = (SELECT MAX(speed) FROM Laptop)

Необходимо сказать несколько слов об автоинкрементируемых столбцах. Если столбец code в таблице Laptop определен как IDENTITY(1,1), то следующий оператор

UPDATE Laptop SET code=5 WHERE code=4

не будет выполнен, т.к. автоикрементируемое поле не допускает обновления, и мы получим соответствующее сообщение об ошибке. Чтобы выполнить все же эту задачу, можно поступить следующим образом. Сначала вставить нужную строку, используя SET IDENTITY_INSERT, после чего удалить старую строку:

SET IDENTITY_INSERT Laptop ON

INSERT INTO Laptop_ID(code, model, speed, ram, hd, price, screen)

SELECT 5, model, speed, ram, hd, price, screen

FROM Laptop_ID WHERE code=4

DELETE FROM Laptop_ID WHERE code=4

Разумеется, другой строки со значением code=5 в таблице быть не должно.

В Transact-SQL оператор UPDATE расширяет стандарт за счет использования необязательного предложения FROM. В этом предложении специфицируется таблица, обеспечивающая критерий для операции обновления. Дополнительную гибкость здесь дает использование операций соединения таблиц.

Пример. Пусть требуется указать "No PC" (нет ПК) в столбце type для тех моделей ПК из таблицы Product, для которых нет соответствующих строк в таблице PC. Решение посредством соединения таблиц можно записать так:

UPDATE Product

SET type='No PC'

FROM Product pr LEFT JOIN PC ON pr.model=pc.model

WHERE type='pc' AND pc.model IS NULL

Здесь используется внешнее соединение, в результате чего столбец pc.model для моделей ПК, отсутствующих в таблице PC, будет содержать NULL-значение, что и используется для идентификации подлежащих обновлению строк. Естественно, эта задача имеет решение и в "стандартном" исполнении:

UPDATE Product

SET type='No PC'