Самоучитель по SQL-сервер в Linux



Модификация записей командой UPDATE

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

UPDATE [ ONLY ] таблица SET

поле = выражение [. ...] [ FROM источник ] [ WHERE условие ]

  • UPDATE [ ONLY ] таблица. Ключевое слово ONLY означает, что обновляется только заданная таблица, но не ее производные таблицы. Применяется лишь в том случае, если таблица использовалась в качестве базовой при наследовании.
  • SET поле = выражение [. ...]. Обязательная секция SET содержит перечисленные через запятую условия, определяющие новые значения обновляемых полей. Условия всегда имеют форму поле = выражение, где поле — имя обновляемого поля (не допускаются ни синонимы, ни точечная запись), а выражение описывает новое значение поля.
  • FROM источник. Секция FROM принадлежит к числу нестандартных расширений PostgreSQL и позволяет обновлять поля значениями, взятыми из других наборов.
  • WHERE условие. В секции WHERE задается критерий, по которому в таблице выбираются обновляемые записи. Если секция WHERE отсутствует, поле обновляется во всех записях. По аналогии с командой SELECT может использоваться для уточнения выборки из источников, перечисленных в секции FROM.

В листинге 4.53 приведен пример простой команды UPDATE. Команда заполняет поле retail таблицы stock вещественной константой 29.95. Секция WHERE ограничивает обновление записями, соответствующими заданному критерию.

Листинг 4.53. Простая команда UPDATE

booktown=# SELECT retail FROM stock

booktown-# WHERE isbn = '0590445065';

retail

23.95

(1 row)

booktown=# UPDATE stock

booktown-# SET retail = 25.95

booktown-# WHERE isbn = '0590445065';

UPDATE 1

booktown=# SELECT retail FROM stock

booktown-# WHERE isbn = '0590445065';

retail

25.95

(1 row)

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

Обновление поля во всех записях таблицы

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

Подвыражение (retail / cost) определяет текущую удельную прибыль, которая увеличивается на 10 % при помощи оператора + и вещественной константы 0.1. Конструкция 0.1:: numeric выполняет явное преобразование вещественной константы к типу numeric; необходимость преобразования объясняется тем, что частное при делении retail /cost относится к типу numeric. Наконец, новая удельная прибыль умножается на стоимость единицы товара из поля cost. Результат равен новой цене, сохраняемой в поле retail.

Листинг 4.54. Обновление поля во всех записях

booktown=# SELECT isbn, retail, cost

booktown-# FROM stock

booktown-# ORDER BY isbn ASC

booktown-# LIMIT 3;

isbn | retail | cost

0385121679 | 36.95 | 29.00

039480001X | 32.95 | 30.00

0394800753 | 16.95 | 16.00

(3 rows)

booktown=# UPDATE stock

booktown-# SET retail =

booktown-# (cost * ((retail / cost) + 0.1::numeric));

UPDATE 16

booktown=# SELECT isbn, retail, cost

booktown-# FROM stock

booktown-# ORDER BY isbn ASC

booktown-# LIMIT 3;

isbn | retail | cost

0385121679 | 39.85 | 29.00

039480001X | 35.95 | 30.00

0394800753 | 18.55 | 16.00

(3 rows)

Команда UPDATE, приведенная в листинге 4.54, не содержит секции WHERE, поэтому обновляются все записи таблицы stock.

Обновление нескольких полей

Перечисление команд присваивания в секции SET через запятую позволяет обновить несколько полей таблицы в одной команде. В листинге 4.55 продемонстрировано одновременное изменение полей name и address таблицы publ i shers для записи с полем id, равным ИЗ.

Листинг 4.55. Выполнение команды UPDATE с обновлением нескольких полей

booktown=# UPDATE publishers

booktown-# SET name = 'OVReilly & Associates',

booktown-# address = 'OVReilly & Associates. Inc. '

booktown-# || '101 Morris St, Sebastopol, CA 95472'

booktown-# WHERE id = 113;

UPDATE 1

booktown=# SELECT name, substrtaddress, 1, 40) || '...' AS short_address

booktown-# FROM publishers

booktown-# WHERE id = 113;

name | short_address

O'Reilly & Associates | O'Reilly & Associates. Inc. 101 Morris S...

(1 row)

В этой команде UPDATE значения обоих полей, name и address, заданы в виде строковых констант. Обратите внимание: внутренние апострофы в строках экранируются обратной косой чертой. Команда SELECT позволяет убедиться в правильности выполненного обновления.

Листинг 4.55 также служит примером практического использования оператора конкатенации || и функции substr(). Полю address присваиваются две строковые константы, объединенные оператором || для предотвращения выхода за пределы командной строки терминала. Затем функция substr() усекает выводимый адрес, чтобы он не переносился на другую строку. В данном случае это сделано лишь для удобства чтения выходных данных. Конечно, если вас интересует полное содержимое поля, усекать его не следует.

Обновление записей по нескольким источникам

В PostgreSQL команда SQL UPDATE была дополнена мощной нестандартной возможностью — поддержкой секции FROM. Секция FROM позволяет получать входные данные из других наборов данных (таблиц и подзапросов).

В листинге 4.56 команда UPDATE с секцией FROM обновляет данные таблицы stock по данным таблицы stock_backup. Секция WHERE описывает связь между обновляемой таблицей и источником. Каждый раз, когда в таблицах находятся совпадающие значения isbn, поле retail в таблице stock обновляется значением из резервной таблицы stock_backup.

Листинг 4.56. Команда UPDATE с несколькими источниками

booktown=# UPDATE stock .

booktown-# SET retail = stock_backup.retail

booktown-# FROM stock_backup

booktown-# WHERE stock.isbn = stock_backup.isbn;

UPDATE 16

Секция FROM поддерживает все разновидности синтаксиса JOIN, описанные в разделе «Выборка данных командой SELECT», что открывает широкие возможности обновления данных в существующих наборах. Более того, как упоминалось выше, в качестве источника данных в секциях FROM могут использоваться подзапросы.




Книжный магазин