UPDATE

UPDATE — изменить строки таблицы

Синтаксис

[ WITH [ RECURSIVE ] запрос_WITH [, ...] ]
UPDATE [ ONLY ] имя_таблицы [ * ] [ [ AS ] псевдоним ]
    SET { имя_столбца = { выражение | DEFAULT } |
          ( имя_столбца [, ...] ) = [ ROW ] ( { выражение | DEFAULT } [, ...] ) |
          ( имя_столбца [, ...] ) = ( подзапрос_SELECT )
        } [, ...]
    [ FROM элемент_FROM [, ...] ]
    [ WHERE условие | WHERE CURRENT OF имя_курсора ]
    [ RETURNING * | выражение_результата [ [ AS ] имя_результата ] [, ...] ]

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

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

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

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

Параметры

запрос_WITH

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

имя_таблицы

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

псевдоним

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

имя_столбца

Имя столбца в таблице с именем имя_таблицы. При необходимости имя столбца можно дополнить именем вложенного поля или индекса в массиве. Имя таблицы добавлять к имени целевого столбца не нужно — например, синтаксис UPDATE table_name SET table_name.col = 1 является недопустимым.

выражение

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

DEFAULT

Установить для столбца значение по умолчанию (которое будет равно NULL, если ему не было присвоено никакого конкретного выражения по умолчанию). В столбце идентификации будет установлено новое значение, сгенерированное соответствующей последовательностью. Для генерируемого столбца это указание допускается, но оно просто задает обычное поведение, при котором этот столбец вычисляется из своего генерирующего выражения.

подзапрос_SELECT

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

элемент_FROM

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

условие

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

имя_курсора

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

выражение_результата

Выражение, которое вычисляется и возвращается командой UPDATE после изменения каждой строки. Это выражение может использовать любые имена столбцов таблицы имя_таблицы или таблиц, перечисленных в списке FROM. Чтобы вернуть все столбцы, напишите *.

имя_результата

Имя, используемое для возвращаемого столбца.

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

После успешного завершения команда 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 на этой новой версии строки.

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

Примеры

Изменение слова 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;

Выполнение той же операции с подзапросом SELECT в предложении 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 salesmen
     WHERE salesmen.id = accounts.sales_id);

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

UPDATE accounts SET contact_first_name = first_name,
                    contact_last_name = last_name
  FROM salesmen WHERE salesmen.id = accounts.sales_id;

Однако если salesmen.id — не уникальный ключ, второй запрос может давать непредсказуемые результаты, тогда как первый запрос гарантированно выдаст ошибку, если найдется несколько записей с одним id. Кроме того, если для конкретной записи в accounts.sales_id не найдется соответствий, первый запрос установит в полях имени 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;

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

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

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

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