Скажу без преувеличения, что инструкция UPDATE является исключительно мощным инструментом. То, что раньше занимало десятки программных строк и несколько вложенных циклов, теперь можно реализовать с помощью всего одной инструкции. SQL нельзя назвать в полной мере настоящим командным языком — он является декларативным. Код SQL всего лишь ставит задачу оптимизатору запросов. После этого оптимизатор разрабатывает наилучший план получения ответа на заданный вопрос и выполняет сформированную задачу. При этом он оценивает, сколько использовать таблиц и в каком порядке, как их объединять и какие индексы использовать. Он учитывает статистику заполнения таблиц и их размеры; производительность процессора и дисковой системы, емкость памяти. Только на основе всей этой информации он способен сформировать план выполнения запроса. Написание программ, обновляющих последовательно строки, никогда не позволило’бы добиться такого уровня оптимизации.

Обновление одной таблицы

Инструкция UPDATE языка SQL предельно проста. Она способна изменить значение всего одной ячейки, а также всех столбцов всех строк таблицы. Однако предложение FROM позволяет таблице стать частью составного источника данных и использовать всю силу инструкции SELECT.

Вот как работает инструкция UPDATE:

UPDATE dbo. таблица

SET столбец = значение,/выражение/столбец, столбец = значение...

[FROM источники_даиных]

[WHERE условия]

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

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

Выполнение глобального поиска и замены

Очистка базы данных от “мусора” представляет собой типичную задачу ее администратора. К счастью, SQL содержит функцию replace (), которая в комбинации с инструкцией UPDATE может помочь в глобальном поиске и замене.

В следующем примере, который ссылается на учебную базу данных Family, мы будем искать вхождения двойной буквы 1 (т.е. “И”) в фамилии и заменять их на “qua”:

Use Family Update Person

Set LastName = Replace(Lastname,    '11',    'qua')

Ссылка на множество таблиц при обновлении

данных

Более мощной функцией инструкции UPDATE является присвоение столбцу результата выражения, которое ссылается на тот же или другие столбцы или даже на другие таблицы.

Хотя выражения доступны и при работе с одной таблицей, чаще всего в них приходится ссылаться на данные, находящиеся вне обновляемой таблицы. Необязательное предложение FROM позволяет создавать объединения между обновляемой таблицей и другими источниками данных. Обновляться может только одна таблица, однако будучи объединенной с другими, она может получать данные из их столбцов и использовать в выражениях обновления.

Одним из способов визуализации объединений, создаваемых в предложении FROM, является слияние всех таблиц в новый единый сверхширокий результирующий набор данных. После этого оставшаяся часть инструкции SQL работает уже с этим новым множеством. Несмотря на то что именно так и работает предложение FROM, реальная инструкция UPDATE работает не с этим множеством, а только с таблицей, указанной сразу после ключевого слова UPDATE.

Синтаксис инструкции update from входит в расширения Т-SQL и не содержится в стандарте ANSI SQL-92. Если в будущем планируется перенос базы данных на другую платформу, то для обновления корректных строк используйте подзапросы.

Рассмотрим пример из реальной жизни. Предположим, что все сотрудники вскоре должны получить повышение зарплаты, размер которой зависит от подразделения, времени нахождения в текущей должности, производственных результатов и срока работы в компании (согласен, в реальной жизни такое вряд ли возможно). Если процент для каждого отдела хранится в таблице Department, то всего одна инструкция UPDATE позволяет объединить ее с таблицей Employee и учесть коэффициент размера премии. Предположим, что формула начислений следующая:

2+(((стаж_в_компании*0,1)+(месяцев_на_должности*0, 02)

+ ((рейтинг_производительности*0, 5) если больше 2))

* коэффициент_отдела)

В предлагаемом ниже сценарии создается несколько таблиц, которые заполняются данными, после этого тестируется формула и в результате выполняется обновление.

USE Tempdb

CREATE TABLE dbo.Dept (

DeptID INT IDENTITY NOT NULL PRIMARY KEY NONCLUSTERED,

DeptName VARCHAR(50) NOT NULL,

RaiseFactor NUMERIC(4,2)

)

ON [Primary]

go

Create TABLE dbo.Employee (

EmployeeID INT IDENTITY NOT NULL PRIMARY KEY NONCLUSTERED,

DeptID INT FOREIGN KEY REFERENCES Dept,

LastName VARCHAR(50) NOT NULL,

FirstName VARCHAR(50) NOT NULL,

Salary INT,

PerformanceRating NUMERIC(4,2),

DateHire DATETIME,

DatePosition DATETIME )

ON [Primary] go

-- создание данных примера

INSERT dbo.Dept    VALUES    ('Engineering'    ,    1.2)

INSERT dbo.Dept    VALUES    ('Sales',.8)

INSERT dbo.Dept    VALUES    (    'IT' ,2.5)

INSERT dbo.Dept    VALUES    ('Manufacturing',1.0)

go

INSERT dbo.Employee

VALUES( 1,'Smith', 'Sam',54000, 2.0,    '1/1/97',    4/4/2001'    )

INSERT dbo.Employee

VALUES( 1,'Nelson','Slim',78000,1.5,    '1/9/88',    '1/1/2000' )

INSERT dbo.Employee

VALUES( 2,'Ball', 'Sally',45000,3.5,' '1/8/99', '1/1/2001' )

INSERT dbo.Employee

VALUES( 2, 'Kelly', 'Jeff',85000,2.4,    '1/10/83',    '1/9/1998'    )

INSERT dbo.Employee

VALUES( 3,    'Guelzow',    'Jo',120000,4.0,    '1/7/95',    '1/6/2001'    )

INSERT dbo.Employee

VALUES( 3, 'Anderson', 'Missy',95000,1.8,    '1/2/99',    '1/9/97'    )

INSERT dbo.Employee

VALUES( 4,'Reagan', 'Frank',75000,2.9,    '1/4/00',    '1/4/2000'    )

INSERT dbo.Employee

VALUES( 4, 'Adams', 'Hank',34000,3.2, '1/9/98', '1/9/1998' )

Предполагая, что датой повышения зарплаты является 1/5/2002, следующий запрос протестирует данные примера:

SELECT LastName, Salary,

DateDiff(уу, DateHire, '1/5/2002') as YearsCo,

DateDiff(mm, DatePosition, '1/5/2002') as MonthPosition,

CASE

WHEN Employee.PerformanceRating >= 2 THEN Employee.PerformanceRating ELSE 0 END as Performance,

Dept.RaiseFactor AS 'Dept'

FROM dbo.Employее JOIN dbo.Dept

ON Employee.DeptID = Dept.DeptID

Должен быть получен следующий результат:

На основании заполненных данных выполним запрос, вычисляющий коэффициент повышения зарплаты:

SELECT LastName,

(2 + (((DateDiff(уу, DateHire, '1/5/2002') * .1)

+ (DateDiff(mm, DatePosition, '1/5/2002') * .02)

+ (CASE

WHEN Employee.PerformanceRating >= 2

THEN Employee.PerformanceRating ELSE 0 END * .5 ))

* Dept.RaiseFactor))/100 as EmpRaise FROM dbo.Employee JOIN dbo.Dept

ON Employee.DeptID = Dept.DeptID

Будет получен следующий результат:

Итак, данные внесены, и формулы проверены. Теперь пришло время выполнить инструкцию UPDATE и скорректировать зарплату сотрудников:

UPDATE Employee SET Salary = Salary *    (1    +

(2 + (((DateDiff(yy, DateHire, ¹1/5/2002') * .1)

+ (DateDiff(mm, DatePosition, '1/5/2002') * .02)

+ (CASE

WHEN Employee.PerformanceRating >= 2 THEN Employee.PerformanceRating ELSE 0 END * .5 ))

* Dept.RaiseFactor))/100 )

FROM dbo.Employee JOIN dbo.Dept

ON Employee.DeptID = Dept.DeptID

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

SELECT FirstName, LastName, Salary FROM Employee

А вот и сам результат:

В завершение нашего примера очистим созданные учебные таблицы:

DROP TABLE dbo.Employee DROP TABLE dbo.Dept

В приведенном выше примере были сведены воедино операции, описанные в нескольких предыдущих главах, в том числе создание и удаление таблиц, выражения CASE, объединения, скалярные функции работы с датами, не говоря уже об операциях вставки и обновления, описанных в настоящей главе. Пример был достаточно длинным, поскольку продемонстрировал больше, нежели одну инструкцию UPDATE. В нем был показан типовой процесс проектирования сложных обновлений, который включает несколько этапов.

1. Проверка доступных данных. Первая инструкция SELECT объединила таблицы Employee и Dept и вывела список всех столбцов, необходимых формуле.

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

3. Выполнение обновления. Как только формула создана и проверена, ее можно подставить в инструкцию UPDATE, которую, в свою очередь, следует выполнить.

Р1нструкцию UPDATE нельзя не назвать мощной. Я заменил исключительно сложные наборы данных и вложенные циклы, выполняющиеся медленно и к тому же подверженные ошибкам, всего одной инструкцией UPDATE с грамотно созданным объединением. В результате время выполнения сократилось с нескольких минут до считанных секунд. В то же время я нисколько не умаляю достоинства подхода к обновлению с помощью наборов, а не строк данных.