UPDATE

примечание

Эта страница переведена при помощи нейросети GigaChat.

UPDATE - обновление строк таблицы.

Синтаксис

[ WITH [ RECURSIVE ] with_query [, ...] ]
UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ]
    SET { column_name = { expression | DEFAULT } |
          ( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |
          ( column_name [, ...] ) = ( sub-SELECT )
        } [, ...]
    [ FROM from_item [, ...] ]
    [ WHERE condition | WHERE CURRENT OF cursor_name ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

Описание

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

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

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

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

Параметры

with_query

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

table_name

Имя таблицы, при необходимости дополненное схемой, которую необходимо обновить. Если перед именем таблицы указано ONLY, то обновление будет выполнено только для указанной таблицы. Если не указано ONLY, то соответствующие строки также будут обновлены во всех таблицах, наследуемых от указанной таблицы. Опционально после имени таблицы можно указать *, чтобы явно указать, что включены дочерние таблицы.

alias

Заменяющее имя целевой таблицы. Когда предоставляется псевдоним, он полностью скрывает фактическое имя таблицы. Например, если дано UPDATE foo AS f, остальная часть предложения UPDATE должна ссылаться на эту таблицу как f, а не foo.

column_name

Имя столбца в таблице table_name. Имя столбца может быть уточнено с помощью имени вложенных полей или индекса массива, если это необходимо. Не включайте имя таблицы в спецификацию целевого столбца — например, UPDATE table_name SET table_name.col = 1 является недопустимым.

expression

Выражение для присвоения столбцу. Выражение может использовать старые значения этого и других столбцов в таблице.

DEFAULT

Присвоение значения по умолчанию (NULL, если оно не задано). Для столбца типа идентичности генерируется новое значение. Для вычисляемых столбцов при использовании DEFAULT вычисление идет по формуле столбца.

sub-SELECT

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

from_item

Выражение таблицы, позволяющее столбцам из других таблиц появляться в условии и выражениях обновления. Это использует тот же синтаксис, что и предложение FROM оператора SELECT, например, можно указать псевдоним для имени таблицы. Не повторяйте целевую таблицу как from_item, если необходимо выполнить замкнутое объединение (в этом случае она должна появиться с псевдонимом в from_item).

condition

Выражение, которое возвращает значение типа boolean. Только строки, для которых это выражение возвращает true, будут обновлены.

cursor_name

Имя курсора, который будет использоваться в условии WHERE CURRENT OF. Обновляемая строка — это последняя строка, извлеченная из этого курсора. Курсор должен быть запросом без группировки к целевой таблице UPDATE. Обратите внимание, что WHERE CURRENT OF нельзя указывать вместе с логическим условием. Смотрите DECLARE для получения дополнительной информации об использовании курсоров с WHERE CURRENT OF.

output_expression

Выражение, которое должно быть вычислено и возвращено командой UPDATE после обновления каждой строки. Выражение может использовать любое имя столбца таблицы с именем table_name или таблиц(ы), перечисленные в FROM. Используйте *, чтобы вернуть все столбцы.

output_name

Имя, назначаемое возвращаемому столбцу.

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

При успешном завершении команда UPDATE возвращает тег команды следующего вида

UPDATE count

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

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

Примечания

В случае использования FROM, целевая таблица соединяется с указанными в from_item таблицами, и каждая результирующая строка означает отдельную операцию обновления. Убедитесь, что для каждой целевой строки получается не более одной выходит строки соединения. Иначе обновится только одна из них, но какую именно — не предсказуемо. Поэтому использование подзапросов часто надежнее, хотя и может быть менее читаемым и медленнее.

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

Существует вероятность того, что одновременная операция UPDATE или DELETE над перемещаемой строкой приведет к ошибке сериализации. Предположим, сеанс 1 выполняет операцию UPDATE с ключом партиции, а между тем параллельный сеанс 2, для которого эта строка видима, выполняет операцию UPDATE или DELETE над этой строкой. В таком случае операции UPDATE или DELETE сеанса 2 обнаружат перемещение строки и вызовут ошибку сериализации (которая всегда возвращается с кодом состояния SQL '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, используя синтаксис предложения 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;
-- other operations
SAVEPOINT sp1;
INSERT INTO wines VALUES('Chateau Lafite 2003', '24');
-- Assume the above fails because of a unique key violation,
-- so now we issue these commands:
ROLLBACK TO sp1;
UPDATE wines SET stock = stock + 24 WHERE winename = 'Chateau Lafite 2003';
-- continue with other operations, and eventually
COMMIT;

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

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

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

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 позволяет команде приоритизировать, какие строки будут обновляться, оно также может предотвратить взаимоблокировку с другими операциями обновления, если они используют тот же порядок сортировки. Если конкуренция за блокировку вызывает беспокойство, то в выражение CTE можно добавить пункт SKIP LOCKED, чтобы предотвратить одновременное обновление одной и той же строки несколькими командами. Однако затем потребуется окончательная команда UPDATE без пунктов SKIP LOCKED или LIMIT, чтобы убедиться, что ни одна совпадающая строка не была упущена из виду.

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

Команда соответствует стандарту SQL, за исключением расширений:

• FROM и RETURNING;
• использования WITH внутри UPDATE.

Некоторые системы позволяют дублировать целевую таблицу в FROM без псевдонима — PostgreSQL так не делает, будьте внимательны при переносе.

По стандарту в подсписке целевых столбцов можно использовать любое выражение строкового типа, дающее нужное число столбцов. PostgreSQL разрешает только конструктор строки или подзапрос. При конструкторе строки можно использовать DEFAULT, в подзапросе — нет.