ITT1504: Базы данных

1234567

Материал предоставлен http://it.rfet.ru

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

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

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

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

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

booktown=# SELECT retail FROM stock WHERE isbn = '0590445065';
retail
23.95
(1 row)

booktown=# UPDATE stock SET retail = 25.95 WHERE isbn = '0590445065';
UPDATE 1

booktown=# SELECT retail FROM stock WHERE isbn = '0590445065';
retail
25.95
(1 row)

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

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

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

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

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

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 SET retail = (cost * ((retail / cost) + 0.1::numeric));
UPDATE 16

booktown=# SELECT isbn, retail, cost
  FROM stock
  ORDER BY isbn ASC
  LIMIT 3;

isbn       | retail| cost
0385121679 | 39.85 | 29.00
039480001X | 35.95 | 30.00
0394800753 | 18.55 | 16.00
(3 rows)

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

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

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

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

booktown=# UPDATE publishers
  SET name = 'O\'Reilly & Associates',
  address = 'O\'Reilly & Associates. Inc. 101 Morris St, Sebastopol, CA 95472'
  WHERE id = 113;

UPDATE 1

booktown=# SELECT name, substr(address, 1, 40) || '…' AS short_address
  FROM publishers
  WHERE id = 113;

name | short_address
O'Reilly & Associates | O'Reilly & Associates. Inc. 101 Morris S…
(1 row)

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

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

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

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

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

booktown=# UPDATE stock
  SET retail = stock_backup.retail
  FROM stock_backup
  WHERE stock.isbn = stock_backup.isbn;

UPDATE 16

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

Сравнение наборов записейУдаление записей командой DELETE