UPDATE

• Синтаксис
• Описание
• Параметры
• Выводимая информация
• Примечания
• Примеры
• Совместимость

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

Синтаксис

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

Описание

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

Существует два способа изменить строки в таблице, используя информацию, содержащуюся в других таблицах базы данных: с помощью подзапросов или указав дополнительные таблицы в предложении 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;

Выполнение той же операции с подзапросом в предложении 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;

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

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

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

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