Описание

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

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

Предложение RETURNING указывает, что команда UPDATE должна вычислить и возвратить значения для каждой фактически изменённой строки. Вычислить в нём можно любое выражение со столбцами целевой таблицы и/или столбцами других таблиц, упомянутых во FROM. При этом в выражении будут использоваться новые (изменённые) значения столбцов таблицы. Список RETURNING имеет тот же синтаксис, что и список результатов SELECT.

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

Параметры

Выводимая информация

В случае успешного завершения, UPDATE возвращает метку команды в виде

UPDATE число

Здесь число обозначает количество изменённых строк, включая те подлежащие изменению строки, значения в которых не были изменены. Заметьте, что это число может быть меньше количества строк, удовлетворяющих условию, когда изменения отменяются триггером BEFORE UPDATE. Если число равно 0, данный запрос не изменил ни одной строки (это не считается ошибкой).

Если команда UPDATE содержит предложение RETURNING, её результат будет похож на результат оператора SELECT (с теми же столбцами и значениями, что содержатся в списке RETURNING), полученный для строк, изменённых этой командой.

Примечания

Когда присутствует предложение FROM, целевая таблица по сути соединяется с таблицами, перечисленными в элементе_FROM, и каждая выходная строка соединения представляет операцию изменения для целевой таблицы. Применяя предложение FROM, необходимо обеспечить, чтобы соединение выдавало максимум одну выходную строку для каждой строки, которую нужно изменить. Другими словами, целевая строка не должна соединяться с более чем одной строкой из других таблиц. Если это условие нарушается, только одна из строк соединения будет использоваться для изменения целевой строки, но какая именно, предсказать нельзя.

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

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

Существует возможность того, что при выполнении другой параллельной операции UPDATE или DELETE с перемещаемой строкой возникнет ошибка сериализации. Например, предположим, что в сеансе 1 выполняется UPDATE для ключа секционирования, а тем временем в параллельном сеансе 2, в котором эта строка видима, выполняется операция UPDATE или DELETE с этой строкой. В этом случае UPDATE/DELETE в сеансе 2 заметит перемещение строки и выдаст ошибку сериализации (которая всегда представляется кодом SQLSTATE '40001'). Получив такую ошибку, приложения могут попытаться повторить транзакцию. В обычном случае, когда таблица не секционирована или строка не перемещается, в сеансе 2 видна изменённая строка, и операция UPDATE/DELETE выполняется с новой версией строки.

Заметьте, что строки могут перемещаться из локальных секций в секцию в сторонней таблице (если обёртка сторонних данных поддерживает перенаправление кортежей), но не из секции в сторонней таблице в другую секцию.

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

Примеры

Изменение слова Drama на Dramatic в столбце kind таблицы films:

UPDATE films SET kind = 'Dramatic' WHERE kind = 'Drama';

Изменение значений температуры и сброс уровня осадков к значению по умолчанию в одной строке таблицы weather:

UPDATE weather SET temp_lo = temp_lo+1, temp_hi = temp_lo+15, prcp = DEFAULT
  WHERE city = 'San Francisco' AND date = '2003-07-03';

Выполнение той же операции с получением изменённых записей:

UPDATE weather SET temp_lo = temp_lo+1, temp_hi = temp_lo+15, prcp = DEFAULT
  WHERE city = 'San Francisco' AND date = '2003-07-03'
  RETURNING temp_lo, temp_hi, prcp;

Такое же изменение с применением альтернативного синтаксиса со списком столбцов:

UPDATE weather SET (temp_lo, temp_hi, prcp) = (temp_lo+1, temp_lo+15, DEFAULT)
  WHERE city = 'San Francisco' AND date = '2003-07-03';

Увеличение счётчика продаж для менеджера, занимающегося компанией Acme Corporation, с применением предложения FROM:

UPDATE employees SET sales_count = sales_count + 1 FROM accounts
  WHERE accounts.name = 'Acme Corporation'
  AND employees.id = accounts.sales_person;

Выполнение той же операции, с вложенным запросом в предложении WHERE:

UPDATE employees SET sales_count = sales_count + 1 WHERE id =
  (SELECT sales_person FROM accounts WHERE name = 'Acme Corporation');

Изменение имени контакта в таблице счетов (это должно быть имя назначенного менеджера по продажам):

UPDATE accounts SET (contact_first_name, contact_last_name) =
    (SELECT first_name, last_name FROM employees
     WHERE employees.id = accounts.sales_person);

Подобный результат можно получить, применив соединение:

UPDATE accounts SET contact_first_name = first_name,
                    contact_last_name = last_name
  FROM employees WHERE employees.id = accounts.sales_person;

Однако если employees.id — не уникальный ключ, второй запрос может давать непредсказуемые результаты, тогда как первый запрос гарантированно выдаст ошибку, если найдётся несколько записей с одним id. Кроме того, если соответствующая запись accounts.sales_person не найдётся, первый запрос запишет в поля имени NULL, а второй вовсе не изменит строку.

Обновление статистики в сводной таблице в соответствии с текущими данными:

UPDATE summary s SET (sum_x, sum_y, avg_x, avg_y) =
    (SELECT sum(x), sum(y), avg(x), avg(y) FROM data d
     WHERE d.group_id = s.group_id);

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

BEGIN;
-- другие операции
SAVEPOINT sp1;
INSERT INTO wines VALUES('Chateau Lafite 2003', '24');
-- Предполагая, что здесь возникает ошибка из-за нарушения уникальности ключа,
-- мы выполняем следующие команды:
ROLLBACK TO sp1;
UPDATE wines SET stock = stock + 24 WHERE winename = 'Chateau Lafite 2003';
-- Продолжение других операций и в завершение...
COMMIT;

Изменение столбца kind таблицы films в строке, на которой в данный момент находится курсор c_films:

UPDATE films SET kind = 'Dramatic' WHERE CURRENT OF c_films;

Одновременное изменение большого количества строк может негативно влиять на производительность, вызывая раздувание таблиц, отставание реплики, повышенный уровень конфликтов блокировок. В таких случаях имеет смысл выполнять операции небольшими порциями, заодно применяя к таблице команду VACUUM между ними. Для команды UPDATE нет предложения LIMIT, но можно добиться похожего эффекта с помощью Общих табличных выражений и замкнутого соединения. Замкнутое соединение на системном столбце ctid эффективно работает со стандартным табличным методом доступа PostgreSQL:

WITH exceeded_max_retries AS (
  SELECT w.ctid FROM work_item AS w
    WHERE w.status = 'active' AND w.num_retries > 10
    ORDER BY w.retry_timestamp
    FOR UPDATE
    LIMIT 5000
)
UPDATE work_item SET status = 'failed'
  FROM exceeded_max_retries AS emr
  WHERE work_item.ctid = emr.ctid;

Эту команду нужно будет повторять до тех пор, пока не останется ни одной строки, требующей изменения. Предложение ORDER BY позволяет указывать, какие строки обновить первыми. Оно также помогает избегать взаимоблокировок с другими операциями изменений, если они запрашивают блокировки в одинаковом порядке. Если существует опасность конфликта блокировок, можно добавить SKIP LOCKED к CTE, чтобы разные команды не могли изменять одну и ту же строку. Однако в таком случае потребуется финальная команда UPDATE без SKIP LOCKED или LIMIT, чтобы убедиться, что никакие строки не были пропущены.

Совместимость

Эта команда соответствует стандарту SQL, за исключением предложений FROM и RETURNING, которые являются расширениями PostgreSQL, как и возможность применять WITH с UPDATE.

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

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