MERGE

MERGE — по условию добавить, обновить или удалить строки таблицы

Синтаксис

[ WITH запрос_WITH [, ...] ]
MERGE INTO [ ONLY ] имя_целевой_таблицы [ * ] [ [ AS ] псевдоним_цели ]
    USING источник_данных ON условие_соединения
    предложение_WHEN [...]

где источником_данных является:

    { [ ONLY ] имя_исходной_таблицы [ * ] | ( исходный_запрос ) } [ [ AS ] псевдоним_источника ]

и предложением_WHEN является:

    { WHEN MATCHED [ AND условие ] THEN { обновление_при_объединении | удаление_при_объединение | DO NOTHING } |
      WHEN NOT MATCHED [ AND условие ] THEN { добавление_при_объединении | DO NOTHING } }

и добавлением_при_объединении является:

    INSERT [( имя_столбца [, ...] )]
        [ OVERRIDING { SYSTEM | USER } VALUE ]
        { VALUES ( { выражение | DEFAULT } [, ...] ) | DEFAULT VALUES }

и обновлением_при_объединении является:

    UPDATE SET { имя_столбца = { выражение | DEFAULT } |
                 ( имя_столбца [, ...] ) = [ ROW ] ( { выражение | DEFAULT } [, ...] ) |
                 ( имя_столбца [, ...] ) = ( вложенный-SELECT )
               } [, ...]

и удалением_при_объединении является:

    DELETE

Описание

Команда MERGE выполняет действия, которые модифицируют строки в целевой таблице, обозначенной как имя_целевой_таблицы, используя источник_данных. MERGE предоставляет один оператор SQL, который может по условию выполнить со строками операторы INSERT, UPDATE или DELETE — задача, которая в ином случае потребовала бы несколько операторов процедурного языка.

Сначала команда MERGE проводит соединение источника_данных с целевой таблицей, производя ноль или более строк-кандидатов на изменение. Для каждой такой строки всего один раз устанавливается статус MATCHED (соответствует) или NOT MATCHED (не соответствует), после чего в заданном порядке вычисляются предложения WHEN. Для каждой строки-кандидата выполняется первое предложение, вычисленное как true. Выполняется для каждой строки-кандидата не более одного предложения WHEN.

Действия команды MERGE имеют тот же эффект, что и у обычных одноименных команд UPDATE, INSERT или DELETE. Однако синтаксис этих команд отличается, в частности, отсутствием предложения WHERE и указания имени таблицы. Все действия относятся к целевой таблице, хотя с помощью триггеров можно проводить модификации других таблиц.

С указанием DO NOTHING исходная строка пропускается. Поскольку действия вычисляются в заданном порядке, с помощью DO NOTHING может быть удобно пропускать не представляющие интерес исходные строки, а потом более детально обрабатывать остальные.

Не существует отдельного права MERGE. При указании действия обновления нужно иметь право UPDATE для столбцов целевой таблицы, на которые ссылается предложение SET. При указании действия добавления или удаления нужно иметь для целевой таблицы право INSERT или DELETE соответственно. При указании действия DO NOTHING нужно иметь право SELECT как минимум для одного столбца целевой таблицы. Кроме того, понадобится право SELECT для любых столбцов источника_данных и целевой таблицы, упомянутых в любом условии (включая условие_соединения) или выражении. Права проверяются один раз при запуске оператора, независимо от того, будут ли выполняться конкретные предложения WHEN.

Команда MERGE не поддерживается, если целевая таблица является материализованным представлением, сторонней таблицей или если для нее определены какие-либо правила.

Параметры

запрос_with

Предложение WITH позволяет указать один или несколько подзапросов, на которые можно ссылаться по имени в запросе MERGE. Подробную информацию см. в разделе Запросы WITH (общие табличные выражения) и на справочной странице команды SELECT. Обратите внимание, что MERGE не поддерживает указание WITH RECURSIVE.

имя_целевой_таблицы

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

псевдоним_цели

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

имя_исходной_таблицы

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

исходный_запрос

Запрос (оператор SELECT или VALUES), предоставляющий строки для объединения в целевой таблице. Описание синтаксиса см. на справочных страницах команды SELECT или VALUES.

псевдоним_источника

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

условие_соединения

условие_соединения — это выражение, выдающее значение типа boolean (аналогично предложению WHERE), которое определяет, какие строки в источнике_данных соответствуют строкам в целевой таблице.

ПРЕДУПРЕЖДЕНИЕ
В условии_соединения должны фигурировать только те столбцы целевой таблицы, по которым проводится попытка сопоставления со строками в источнике_данных. Подвыражения условия_соединения, ссылающиеся только на столбцы целевой таблицы, могут влиять на проводимое действие, зачастую неожиданным образом.

предложение_when

Требуется хотя бы одно предложение WHEN.
Если в предложении WHEN указывается WHEN MATCHED и строка-кандидат на изменение соответствует строке в целевой таблице, то предложение WHEN выполняется, если условие отсутствует или вычисляется в true.
И наоборот, если в предложении WHEN указывается WHEN NOT MATCHED и строка-кандидат на изменение не соответствует строке в целевой таблице, то предложение WHEN выполняется, если условие отсутствует или вычисляется в true.

условие

Выражение, возвращающее значение типа boolean. Если это выражение для предложения WHEN возвращает true, для этой строки выполняется действие для этого предложения.
Условие в предложении WHEN MATCHED может ссылаться на столбцы как исходного, так и целевого отношений. Условие в предложении WHEN NOT MATCHED может ссылаться только на столбцы из исходного отношения, поскольку соответствующая целевая строка отсутствует по определению. В целевой таблице доступны только системные атрибуты.

добавление_при_объединении

Спецификация действия INSERT, добавляющего одну строку в целевую таблицу. Имена целевых столбцов могут быть перечислены в любом порядке. Если список имен столбцов вообще не задан, по умолчанию задействуются все столбцы таблицы в порядке их объявления.
Каждый столбец, отсутствующий в явном или неявном списке столбцов, будет заполнен значением по умолчанию, если оно задано, или, если таковое отсутствует, значением NULL.
Если целевая таблица является партиционированной, каждая строка направляется в подходящую партицию и добавляется в нее. Если целевая таблица является партицией и какая-либо из входных строк нарушает ограничение этой партиции, будет выдана ошибка.
Имена столбцов нельзя указывать более одного раза. Действия INSERT не могут содержать вложенные запросы SELECT. Предложение VALUES можно указать только один раз. Оно может ссылаться только на столбцы из исходного отношения, поскольку соответствующие целевые строки отсутствуют по определению.

обновление_при_объединении

Спецификация действия UPDATE, обновляющего текущую строку целевой таблицы. Имена столбцов нельзя указывать более одного раза.
Не допускается ни имя таблицы, ни предложение WHERE.

удаление_при_объединении

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

имя_столбца

Имя столбца в целевой таблице. При необходимости имя столбца можно дополнить именем подполя или индексом массива. (При добавлении данных только в некоторые поля составного столбца остальные пола заполняются NULL.) Не включайте в спецификацию целевого столбца имя таблицы.

OVERRIDING SYSTEM VALUE

Без этого предложения для столбца идентификации, определенного как GENERATED ALWAYS, добавление явного значения (кроме DEFAULT) является ошибкой. Данное предложение переопределяет это ограничение.

OVERRIDING USER VALUE

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

DEFAULT VALUES

Все столбцы будут заполнены их значениями по умолчанию. (Предложение OVERRIDING в этой форме не допускается.)

выражение

Выражение для присвоения столбцу. При указании в предложении WHEN MATCHED это выражение может использовать значения из исходной строки в целевой таблице и значения из строки источника_данных. При указании в предложении WHEN NOT MATCHED это выражение может использовать значения из строки источника_данных.

DEFAULT

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

вложенный-SELECT

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

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

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

MERGE общее_число

Здесь общее_число является количеством измененных строк (добавленных, обновленных или удаленных). Если общее_число равно 0, не была изменена ни одна строка.

Примечания

Во время выполнения MERGE производятся следующие действия.

  1. Вызываются все триггеры BEFORE STATEMENT для всех указанных действий, независимо от того, совпадают ли их предложения WHEN.

  2. Выполняется соединение исходной и целевой таблиц. Результирующий запрос будет оптимизирован как обычно и выдаст набор строк-кандидатов на изменение. Для каждой строки-кандидата на изменение:

    1. Для каждой строки вычисляется состояние: соответствует (MATCHED) или не соответствует (NOT MATCHED).

    2. Проверяется каждое условие WHEN в указанном порядке, пока какое-либо из них не вернет true.

    3. Когда условие возвращает true, выполняются следующие действия:

      • Выполняются все триггеры BEFORE ROW, срабатывающие для типа события данного действия.

      • Выполняется указанное действие, вызывая все проверочные ограничения для целевой таблицы.

      • Выполняются все триггеры AFTER ROW, срабатывающие для типа события данного действия.

  3. Выполняются все триггеры AFTER STATEMENT для указанных действий, независимо от того, произошли ли они на самом деле. Это похоже на поведение оператора UPDATE, который не модифицирует ни одной строки.

Резюмируя вышесказанное, триггеры уровня оператора для типа события (скажем, INSERT) будут срабатывать каждый раз, когда мы указываем действие этого вида. И наоборот, триггеры уровня строк сработают только для конкретного типа события, которое выполняется. Поэтому команда MERGE может запускать триггеры как для UPDATE, так и для INSERT, даже если на уровне строк сработали только триггеры UPDATE.

Следует позаботиться о том, чтобы для каждой целевой строки соединение выдавало не более одной строки-кандидата на изменение. Другими словами, целевая строка не должна соединяться с более чем одной строкой источника данных. Если же она это делает, то для модификации целевой строки будет использована только одна из строк-кандидатов на изменение; дальнейшие попытки модифицировать эту строку вызовут ошибку. Кроме того, ошибка может произойти, если триггеры строк вносят изменения в целевую таблицу, и строки, модифицированные таким образом, впоследствии также модифицируются командой MERGE. Если повторяемым действием является INSERT, это вызовет нарушение уникальности, тогда как повторяемые UPDATE или DELETE вызовут нарушение кардинальности; поведение в последнем случае требуется стандартом SQL. Это отличается от традиционного для QHB поведения соединений в операторах UPDATE и DELETE, где вторая и последующие попытки модифицировать одну и ту же строку просто игнорируются.

Если в предложении WHEN опускается вложенное предложение AND, оно становится последним достижимым предложением этого вида (MATCHED или NOT MATCHED). Если указывается последующее предложение WHEN этого вида, скорее всего оно будет недостижимым, и возникнет ошибка. Если не указывается никакое последнее достижимое предложение любого вида, возможен сценарий, когда для строки-кандидата на изменение не будет произведено никаких действий.

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

В команде MERGE отсутствует предложение RETURNING. Действия INSERT, UPDATE и DELETE не могут содержать предложения RETURNING или WITH.

Когда MERGE выполняется параллельно с другими командами, модифицирующими целевую таблицу, применяются обычные правила изоляции транзакций; разъяснения по поведению на каждом уровне изоляции см. в разделе Изоляция транзакций. Также можно рассмотреть использование в качестве альтернативы оператора INSERT ... ON CONFLICT, предлагающего выполнить UPDATE, если параллельно выполняется INSERT. У этих двух типов операторов имеется множество различий и ограничений, и они не являются взаимозаменяемыми.

Примеры

Выполнение обновления клиентских счетов (customer_accounts) на основании данных из новых недавних транзакций (recent_transactions).

MERGE INTO customer_account ca
USING recent_transactions t
ON t.customer_id = ca.customer_id
WHEN MATCHED THEN
  UPDATE SET balance = balance + transaction_value
WHEN NOT MATCHED THEN
  INSERT (customer_id, balance)
  VALUES (t.customer_id, t.transaction_value);

Обратите внимание, что это будет полностью равнозначно следующему оператору, поскольку результат MATCHED во время выполнения не меняется.

MERGE INTO customer_account ca
USING (SELECT customer_id, transaction_value FROM recent_transactions) AS t
ON t.customer_id = ca.customer_id
WHEN MATCHED THEN
  UPDATE SET balance = balance + transaction_value
WHEN NOT MATCHED THEN
  INSERT (customer_id, balance)
  VALUES (t.customer_id, t.transaction_value);

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

MERGE INTO wines w
USING wine_stock_changes s
ON s.winename = w.winename
WHEN NOT MATCHED AND s.stock_delta > 0 THEN
  INSERT VALUES(s.winename, s.stock_delta)
WHEN MATCHED AND w.stock + s.stock_delta > 0 THEN
  UPDATE SET stock = w.stock + s.stock_delta
WHEN MATCHED THEN
  DELETE;

Таблица wine_stock_changes может быть, к примеру, временной таблицей, недавно загруженной в базу данных.

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

Эта команда соответствует стандарту SQL.

Предложение WITH и действие DO NOTHING являются расширениями стандарта SQL.

Copyright © KVANTOM LLC, 2019-2025