Модификация записей командой 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 могут использоваться подзапросы.
