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