MERGE#

MERGE

MERGE

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

Синтаксис

[ WITH with_query [, ...] ]
MERGE INTO [ ONLY ] target_table_name [ * ] [ [ AS ] target_alias ]
USING data_source ON join_condition
when_clause [...]
[ RETURNING { * | output_expression [ [ AS ] output_name ] } [, ...] ]

where data_source is:

{ [ ONLY ] source_table_name [ * ] | ( source_query ) } [ [ AS ] source_alias ]

and when_clause is:

{ WHEN MATCHED [ AND condition ] THEN { merge_update | merge_delete | DO NOTHING } |
  WHEN NOT MATCHED BY SOURCE [ AND condition ] THEN { merge_update | merge_delete | DO NOTHING } |
  WHEN NOT MATCHED [ BY TARGET ] [ AND condition ] THEN { merge_insert | DO NOTHING } }

and merge_insert is:

INSERT [( column_name [, ...] )]
[ OVERRIDING { SYSTEM | USER } VALUE ]
{ VALUES ( { expression | DEFAULT } [, ...] ) | DEFAULT VALUES }

and merge_update is:

UPDATE SET { column_name = { expression | DEFAULT } |
             ( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |
             ( column_name [, ...] ) = ( sub-SELECT )
           } [, ...]

and merge_delete is:

DELETE

Описание

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

Сначала команда MERGE выполняет соединение из data_source с целевой таблицей, создавая ноль или более строк-кандидатов для изменения. Для каждой строки-кандидата устанавливается статус MATCHED, NOT MATCHED BY SOURCE, или NOT MATCHED [BY TARGET] только один раз, после чего условия WHEN оцениваются в указанном порядке. Для каждой строки-кандидата выполняется первое условие, которое оценивается как истинное. Не более одной конструкции WHEN выполняется для любой строки-кандидата.

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

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

Необязательное предложение RETURNING заставляет MERGE вычислять и возвращать значение(я) на основе каждой вставленной, обновленной или удаленной строки. Можно вычислить любое выражение, использующее столбцы исходной или целевой таблицы, или функцию merge_action(). Когда выполняется действие INSERT или UPDATE, используются новые значения столбцов целевой таблицы. Когда выполняется DELETE, используются старые значения столбцов целевой таблицы. Синтаксис списка RETURNING идентичен синтаксису списка вывода SELECT.

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

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

Параметры

with_query

Предложение WITH позволяет указать один или несколько подзапросов, которые могут быть использованы по имени в запросе MERGE. Подробности см. в Раздел 7.8 и SELECT. Обратите внимание, что WITH RECURSIVE не поддерживается командой MERGE.

target_table_name

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

Если target_table_name является представлением, оно должно либо автоматически обновляться без триггеров INSTEAD OF, либо иметь триггеры INSTEAD OF для каждого типа действия (INSERT, UPDATE и DELETE), указанного в предложениях WHEN. Представления с правилами не поддерживаются.

target_alias

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

source_table_name

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

source_query

Запрос (SELECT оператор или VALUES оператор), который предоставляет строки для слияния с целевой таблицей. Обратитесь к SELECT оператору или VALUES оператору для описания синтаксиса.

source_alias

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

join_condition

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

Предупреждение

Только столбцы из целевой таблицы, которые пытаются соответствовать data_source строкам, должны появляться в join_condition. Подвыражения join_condition, которые ссылаются только на столбцы целевой таблицы, могут влиять на то, какое действие будет предпринято, часто неожиданными способами.

Если указаны оба условия WHEN NOT MATCHED BY SOURCE и WHEN NOT MATCHED [BY TARGET], команда MERGE выполнит FULL соединение между data_source и целевой таблицей. Для этого необходимо, чтобы хотя бы одно подвыражение join_condition использовало оператор, поддерживающий хеш-соединение, или все подвыражения должны использовать операторы, поддерживающие слияние соединений.

when_clause

Необходимо хотя бы одно предложение WHEN.

Клаузула WHEN может указывать WHEN MATCHED, WHEN NOT MATCHED BY SOURCE или WHEN NOT MATCHED [BY TARGET]. Обратите внимание, что стандарт SQL определяет только WHEN MATCHED и WHEN NOT MATCHED (что означает отсутствие соответствующей строки в целевой таблице). WHEN NOT MATCHED BY SOURCE является расширением стандарта SQL, как и возможность добавления BY TARGET к WHEN NOT MATCHED, чтобы сделать его значение более явным.

Если WHEN условие указывает WHEN MATCHED и строка изменения-кандидата соответствует строке в data_source строке в целевой таблице, условие WHEN выполняется, если condition отсутствует или оценивается как true.

Если WHEN условие указывает WHEN NOT MATCHED BY SOURCE и строка изменения-кандидата представляет строку в целевой таблице, которая не соответствует строке в data_source, то WHEN условие выполняется, если condition отсутствует или оценивается как true.

Если WHEN условие указывает WHEN NOT MATCHED [BY TARGET] и строка изменения-кандидата представляет строку в data_source, которая не соответствует строке в целевой таблице, WHEN условие выполняется, если condition отсутствует или оценивается как true.

condition

Выражение, которое возвращает значение типа boolean. Если это выражение для предложения WHEN возвращает true, то действие для этого предложения выполняется для этой строки.

Условие в WHEN MATCHED может ссылаться на столбцы как в исходных, так и в целевых отношениях. Условие в WHEN NOT MATCHED BY SOURCE может ссылаться только на столбцы из целевого отношения, так как по определению нет соответствующей строки источника. Условие в WHEN NOT MATCHED [BY TARGET] может ссылаться только на столбцы из исходного отношения, так как по определению нет соответствующей строки цели. Доступны только системные атрибуты из целевой таблицы.

merge_insert

Спецификация действия INSERT, которое вставляет одну строку в целевую таблицу. Имена целевых столбцов могут быть перечислены в любом порядке. Если вообще не указан список имен столбцов, то по умолчанию используются все столбцы таблицы в их объявленном порядке.

Каждый столбец, отсутствующий в явном или неявном списке столбцов, будет заполнен значением по умолчанию, либо его объявленным значением по умолчанию, либо значением null, если такового нет.

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

Имена столбцов нельзя указать более одного раза. Действия INSERT не могут содержать подзапросы.

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

merge_update

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

Не разрешается использование ни имени таблицы, ни предложения WHERE.

merge_delete

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

column_name

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

OVERRIDING SYSTEM VALUE

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

OVERRIDING USER VALUE

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

DEFAULT VALUES

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

expression

Выражение для присвоения столбцу. Если используется в WHEN MATCHED условии, выражение может использовать значения из оригинальной строки в целевой таблице и значения из строки data_source. Если используется в WHEN NOT MATCHED BY SOURCE условии, выражение может использовать только значения из оригинальной строки в целевой таблице. Если используется в WHEN NOT MATCHED [BY TARGET] условии, выражение может использовать только значения из строки data_source.

DEFAULT

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

sub-SELECT

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

output_expression

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

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

output_name

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

Выводы

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

MERGE total_count

total_count - это общее количество измененных строк (вставленных, обновленных или удаленных). Если total_count равно 0, ни одна строка не была изменена.

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

Примечания

Следующие шаги выполняются во время выполнения MERGE.

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

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

    1. Оцените, соответствует ли каждая строка MATCHED, NOT MATCHED BY SOURCE или NOT MATCHED [BY TARGET].

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

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

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

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

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

      Если целевое отношение является представлением с INSTEAD OF ROW триггерами для типа события действия, они используются для выполнения действия вместо этого.

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

Вкратце, триггеры операторов для типа события (скажем, INSERT) будут срабатывать каждый раз, когда мы указываем действие такого рода. В отличие от этого, триггеры на уровне строк будут срабатывать только для конкретного типа события, которое выполняется. Таким образом, команда MERGE может вызывать триггеры операторов как для UPDATE, так и для INSERT, даже если сработали только триггеры строк UPDATE.

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

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

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

Когда MERGE выполняется одновременно с другими командами, изменяющими целевую таблицу, применяются обычные правила изоляции транзакций; см. Раздел 13.2 для объяснения поведения на каждом уровне изоляции. Возможно, вам также стоит рассмотреть использование 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
RETURNING merge_action(), w.*;

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

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

MERGE INTO wines w
USING new_wine_list s
ON s.winename = w.winename
WHEN NOT MATCHED BY TARGET THEN
  INSERT VALUES(s.winename, s.stock)
WHEN MATCHED AND w.stock != s.stock THEN
  UPDATE SET stock = s.stock
WHEN NOT MATCHED BY SOURCE THEN
  DELETE;

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

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

Клаузула WITH, квалификаторы BY SOURCE и BY TARGET для WHEN NOT MATCHED, действие DO NOTHING, и клаузула RETURNING являются расширениями стандарта SQL.