2.17. Изменение данных

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

Для изменения данных в записях таблицы используется оператор UPDATE. Общий вид этого оператора можно увидеть в листинге 2.6.

Листинг 2.6. Общий вид команды UPDATE

UPDATE 
  { 
   table_name WITH ( < table_hint_limited > [ ...n ] ) 
   | view_name 
    | rowset_function_limited 
   } 
   SET 
    { column_name = { expression | DEFAULT | NULL } 
        | @variable = expression 
        | @variable = column = expression } [ ,...n ] 

    { { [ FROM { < table_source > } [ ,...n ] ] 

        [ WHERE 
            < search_condition > ] } 
        | 
        [ WHERE CURRENT OF 
        { { [ GLOBAL ] cursor_name } | cursor_variable_name } 
        ] } 
        [ OPTION ( < query_hint > [ ,...n ] ) ] 

Самый простейший вариант команды выглядит следующим образом:

UPDATE Таблица
SET Поле=Значение

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

Давайте изменим дату рождения у всех записей в таблице tbPeoples:

UPDATE tbPeoples
SET dDateBirthDay='10.10.2004'

Можете запустить этот пример и обновить поле "dDateBirthDay" во всех строках, а можете поверить мне на слово, что он работает и не портить красоту данных. Просто сейчас незачем делать значение поля даты одинаковым для всех работников.

Давайте лучше посмотрим другой пример, как можно изменить значение поля. Попробуем увеличить значение даты у всех строк на единицу. Я не зря выбрал дату. Если бы это было число, то оно увеличилось бы на 1, и это было бы вполне логично и понятно. А что произойдет с датой? Выполните запрос SELECT к таблице tbPeoples, и запомните несколько значений. Теперь выполним следующий запрос на обновление:

UPDATE tbPeoples
SET dDateBirthDay = dDateBirthDay + 1

В данном примере мы устанавливаем полю "dDateBirthDay" значение "dDateBirthDay" плюс единица. Снова посмотрим содержимое таблицы tbPeoples. Обратите внимание, что все даты увеличились на один день.

Да, даты можно складывать с числами, потому что реально дата – это число. Более подробно о датах мы поговорим в главе 2.22.

Из этого примера видно, что при использовании оператора UPDATE можно пользоваться математическими вычислениями. Прекрасно, потому что это очень удобно.

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

UPDATE tbPeoples
SET dDateBirthDay='1.1.2004'
WHERE vcFamil='ИВАНОВ' AND vcName='ИВАН'

Если вы выполните этот запрос в программе Query Analyzer, то увидите, что обновилось две записи. Дело в том, что в нашей таблице два работника с таким именем. Вы должны учитывать эту особенность, когда используете оператор UPDATE, ведь он может обновить любое количество записей в таблице, если они удовлетворяют указанным условиям. Старайтесь четко указать, что и как вы хотите обновлять, поэтому при обновлении лучше использовать ключевое поле, которое может четко идентифицировать в таблице нужную строку.

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

SELECT * 
FROM tbPeoples
WHERE vcFamil='ИВАНОВ' AND vcName='ИВАН'

Выполняем запрос и смотрим результат. Если все устраивает, и мы увидели только те строки, которые должны измениться, то заменяем первые две строчки запроса SELECT на операторы UPDATE и SET.

Если вы заранее знаете, что нужно обновить только одну запись (например, конкретного работника), то необходимо убедиться, что будет обновляться только одна строка. Как мы уже знаем, по фамилии искать бесполезно, бывают работники с одинаковыми фамилиями. Но у нас же есть в поле первичный ключ. Именно он может однозначно идентифицировать строку, и две записи таблицы не могут содержать в первичном ключе одно и то же значение:

UPDATE tbPeoples
SET dDateBirthDay='1.1.2004'
WHERE idPeoples=10

Тут очень важно создать механизм определения первичного ключа, но это уже ложиться на программиста клиентских программ для конечного пользователя. Наша задача – SQL запросы.

Обновлять можно и на основе двух таблиц сразу. Например, допустим, что необходимо установить новый номер телефона для работника по фамилии 'КОНОНОВ', и при этом тип телефона должен быть равен 1. Как написать такой запрос обновления? Чтобы вам проще было создавать такой запрос в будущем, давайте рассмотрим логику создания, которая опять же основывается на запросе выборки данных SELECT.

Для начала пишем запрос SELECT на выборку данных необходимого работника:

SELECT * 
FROM tbPeoples pl, tbPhoneNumbers pn
WHERE pl.idPeoples=pn.idPeoples
 AND vcFamil='КОНОНОВ'
 AND idPhoneType=1

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

Как теперь из запроса SELECT делаем UPDATE. Для этого первую строку (оператор SELECT) заменяем на оператор UPDATE, и указываем изменяемое поле:

UPDATE tbPhoneNumbers 
SET vcPhoneNumber='(905) 100-10-10'
FROM tbPeoples pl, tbPhoneNumbers pn
WHERE pl.idPeoples=pn.idPeoples
 AND vcFamil='КОНОНОВ'
 AND idPhoneType=1

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

Если запрос SELECT написан верно, и в его результате есть изменяемое поле, то изменив первую строку на UPDATE и оператор SET, вы верно напишете SQL запрос на изменение нужной строки и нужного поля.

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

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

UPDATE tbPeoples
SET dDateBirthDay=dDateBirthDay+pn.idPhoneNumbers
FROM tbPeoples pl, tbPhoneNumbers pn
WHERE pl.idPeoples=pn.idPeoples
 AND pl.idPeoples=1
 AND idPhoneType=1

А что, если нужно обновить сразу несколько полей? В этом случае в секции SET нужно перечислить операции изменения через запятую:

UPDATE tbPeoples
SET dDateBirthDay=dDateBirthDay+1, 
   vcFamil='mr.'+vcFamil

В этом примере, после ключевого слова SET мы изменяем поле "dDateBithDay", увеличивая его на 1 и поле "vcName", добавляя в начало фамилии префикс mr.

Следующий пример изменяет содержимое двух полей "vcPhoneNumbers" и "vcName" в связанных таблицах:

UPDATE tbPhoneNumbers 
SET vcPhoneNumber='(905) 100-10-10', 
    vcName='Иван'
FROM tbPeoples pl, tbPhoneNumbers pn
WHERE pl.idPeoples=pn.idPeoples
 AND vcFamil='КОНОНОВ'
 AND idPhoneType=1

Количество изменяемых полей в одном запросе не ограничено, можно изменить хоть все поля. Но при этом вы должны учитывать, что нельзя изменять автоматически увеличиваемые поля. Мы не могли записать в такие поля значения при добавлении записей и не можем изменить их. Конечно же, мы можем насильно разрешить изменение поля, установив свойство IDENTITY_INSERT в ON:

SET IDENTITY_INSERT tbPeoples ON

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