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 можно, только используя несколько операторов процедурного языка.
Сначала команда MERGE выполняет соединение источника_данных с целевой таблицей, формируя ноль или более строк-кандидатов на изменение. Для каждой строки-кандидата устанавливается неизменяемый позже статус MATCHED (совпадает) или NOT MATCHED (не совпадает), после чего вычисляются условия WHEN в заданном порядке. Для каждой отдельной строки будет выполняться действие первого же предложения, условие которого выдаст true. При этом для каждой строки-кандидата может быть выполнено действие не более чем одного предложения WHEN.
Действия операции MERGE имеют тот же эффект, что и обычные одноимённые команды UPDATE, INSERT или DELETE. Синтаксис этих команд в MERGE отличается, в частности, отсутствием предложения WHERE и имени таблицы. Действия этих команд выполняются с целевой таблицей, хотя посредством триггеров могут быть изменены и другие таблицы.
С указанием DO NOTHING исходная строка пропускается. Поскольку применимость действий оценивается в заданном порядке, используя DO NOTHING, удобно пропускать исходные строки, не представляющие интерес, чтобы затем более детально обрабатывать остальные.
Для команды MERGE не предусмотрено отдельное право. Если пользователь указывает в ней действие UPDATE, у него должно быть право UPDATE для столбцов целевой таблицы, на которые ссылается предложение SET. Когда указывается действие INSERT или DELETE, у пользователя должно быть соответствующее право для целевой таблицы. Если пользователь указывает действие DO NOTHING, у него должно быть право SELECT хотя бы для одного столбца целевой таблицы. Кроме того, необходимо иметь право SELECT для любых столбцов источника_данных и целевой таблицы, которые фигурируют в condition (в том числе join_condition) или expression. Права проверяются один раз в начале выполнения оператора, вне зависимости от того, будут ли выполняться конкретные предложения WHEN.
Оператор MERGE не поддерживается для целевых таблиц, являющихся материализованными представлениями, сторонними таблицами, или если для них заданы какие-либо правила.
Параметры
запрос_WITHПредложение
WITHпозволяет задать один или несколько подзапросов, на которые затем можно ссылаться по имени в запросеMERGE. За подробностями обратитесь к Разделу 7.8 и SELECT. Обратите внимание, что предложениеWITH RECURSIVEдля командыMERGEне поддерживается.имя_целевой_таблицыИмя (возможно, дополненное схемой) целевой таблицы, принимающей результат объединения. Если перед именем таблицы добавлено
ONLY, соответствующие строки изменяются или удаляются только в указанной таблице. БезONLYсоответствующие строки также изменяются или удаляются во всех таблицах, унаследованных от указанной таблицы. При желании, после имени таблицы можно указать*, чтобы явно обозначить, что операция затрагивает все дочерние таблицы. Ключевое словоONLYи параметр*не влияют на действияINSERT, добавляющие строки только в указанную таблицу.целевой_псевдонимАльтернативное имя целевой таблицы. Когда это имя задаётся, настоящее имя таблицы полностью скрывается. Например, в запросе
MERGE INTO foo AS fостальные компоненты оператораMERGEдолжны обращаться к целевой таблице по имениf, а неfoo.имя_исходной_таблицыИмя (возможно, дополненное схемой) исходной таблицы, представления или переходной таблицы. Если перед именем таблицы добавлено
ONLY, соответствующие строки берутся только из указанной таблицы. БезONLYстроки также берутся из всех таблиц, унаследованных от указанной. При желании, после имени таблицы можно указать*, чтобы явно обозначить, что операция затрагивает все дочерние таблицы.исходный_запросЗапрос (оператор
SELECTили операторVALUES), предоставляющий строки для объединения в целевой таблице. За информацией о синтаксисе обратитесь к описанию SELECT и VALUES.исходный_псевдонимАльтернативное имя для источника данных. Когда задаётся этот псевдоним, он полностью скрывает настоящее имя таблицы или тот факт, что это результат запроса.
условие_соединенияЗадаваемое
условие_соединенияпредставляет собой выражение, выдающее значение типаboolean(как в предложенииWHERE), которое определяет, какие строки висточнике_данныхсоответствуют строкам в целевой таблице.Предупреждение
В
условии_соединениядолжны фигурировать только столбцы целевой таблицы, по которым её строки сопоставляются со строкамиисточника_данных. Подвыраженияусловия_соединения, ссылающиеся только на столбцы целевой таблицы, могут влиять на выполняемое действие, часто неожиданным образом.предложение_whenВ команде
MERGEдолжно быть минимум одно предложение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Без этого предложения не допускается задание явного значения (отличного от
DEFAULT) для столбца идентификации, определённого с характеристикойGENERATED ALWAYS. Данное предложение перекрывает это ограничение.OVERRIDING USER VALUEЕсли указывается это предложение, то значения, заданные для столбцов идентификации, которые определены с характеристикой
GENERATED BY DEFAULT, игнорируются и вместо них применяются значения, выдаваемые последовательностями по умолчанию.DEFAULT VALUESВсе столбцы получают значения по умолчанию. (Предложение
OVERRIDINGв этой форме не допускается.)выражениеВыражение, результат которого присваивается столбцу. В выражениях предложений
WHEN MATCHEDмогут использоваться значения из исходной строки целевой таблицы и значения из строкиисточника_данных. В выражениях предложенийWHEN NOT MATCHEDмогут использоваться значения только изисточника_данных.DEFAULTПрисвоить столбцу значение по умолчанию (или
NULL, если выражение по умолчанию для столбца не определено).вложенный_SELECTПодзапрос
SELECT, выдающий столько выходных столбцов, сколько перечислено в предшествующем ему списке столбцов в скобках. При выполнении этого подзапроса должна быть получена максимум одна строка. Если он выдаёт одну строку, значения столбцов в нём присваиваются целевым столбцам; если же он не возвращает строку, целевым столбцам присваивается NULL. Этот подзапрос может обращаться к значениям исходной строки в целевой таблице, а также значениям строкиисточника_данных.
Выводимая информация
При успешном выполнении команда MERGE возвращает метку команды в виде
MERGE общее_число
Здесь общее_число — суммарное количество изменённых строк (добавленных, изменённых или удалённых). Если общее_число равно 0, ни одна строка не была изменена.
Примечания
В ходе выполнения MERGE производятся следующие действия.
Вызываются все триггеры
BEFORE STATEMENTдля всех указанных действий, независимо от того, совпадают ли их предложенияWHEN.Выполняется соединение исходной таблицы с целевой. Полученный в результате запрос оптимизируется как обычно и выдаёт набор строк-кандидатов на изменение. Для каждой строки-кандидата на изменение:
Для каждой строки определяется состояние:
MATCHED(совпадает) илиNOT MATCHED(не совпадает).Проверяется каждое условие
WHENв заданном порядке, пока какое-либо не выдаст значение true.Если условие выдаёт true, происходит следующее:
Вызываются все триггеры
BEFORE ROW, соответствующие типу события выполняемого действия.Выполняется указанное действие, при этом вызываются ограничения-проверки для целевой таблицы.
Вызываются все триггеры
AFTER ROW, соответствующие типу события выполняемого действия.
Выполняются все триггеры
AFTER STATEMENTдля всех указанных действий, независимо от того, выполнялись ли эти действия фактически. Это похоже на поведение оператораUPDATE, когда он не меняет ни одной строки.
То есть триггеры уровня оператора для некоторого события (скажем, INSERT) будут вызываться всегда, когда указывается действие такого типа. Триггеры уровня строк, напротив, вызываются только для определённого действия, которое выполняется. Таким образом, при выполнении MERGE могут вызываться триггеры уровня оператора как для UPDATE, так и для INSERT, даже если на уровне строк вызывались только триггеры UPDATE.
Следует позаботиться о том, чтобы для каждой целевой строки в результате соединения создавалось не более одной строки-кандидата на изменение. Другими словами, целевая строка не должна соединяться с более чем одной строкой источника данных. Если это не так, только одна из строк-кандидатов будет применяться для изменения целевой строки; последующие попытки изменить эту строку вызовут ошибку. Ошибка также может произойти, когда триггеры строк вносят изменения в целевую таблицу, а команда MERGE впоследствии воздействует на уже изменённые строки. Если повторится действие INSERT, это вызовет нарушение уникальности, а повторение UPDATE или DELETE вызовет ошибку «Нарушение количества»; последнее требуется стандартом SQL. Такое поведение отличается от поведения соединений в UPDATE и DELETE, традиционного для PostgreSQL, когда вторая и последующие попытки изменить одну и ту же строку просто игнорируются.
Если в предложении WHEN отсутствует дополнительное условие AND, оно становится последним достижимым предложением этого рода (MATCHED или NOT MATCHED). Если в команде встретится последующее предложение WHEN такого рода, оно гарантированно будет недостижимым, и это вызовет ошибку. В случае отсутствия последнего достижимого предложения любого рода возможна ситуация, когда для строки-кандидата на изменение не будет предпринято никаких действий.
Порядок, в котором строки выдаются из источника данных, по умолчанию не определён. Если необходим определённый порядок, например для предотвращения взаимоблокировок между параллельными транзакциями, его можно задать в исходном_запросе.
В операторе MERGE не допускается предложение RETURNING. Действия INSERT, UPDATE и DELETE также не могут содержать предложения RETURNING или WITH.
Когда 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;
Таблица wine_stock_changes может быть, например, временной таблицей, недавно загруженной в базу данных.
Совместимость
Эта команда соответствует стандарту SQL.
Предложение WITH и действие DO NOTHING являются расширениями стандарта SQL.
MERGE
MERGE — conditionally insert, update, or delete rows of a table
Synopsis
[ WITHwith_query[, ...] ] MERGE INTO [ ONLY ]target_table_name[ * ] [ [ AS ]target_alias] USINGdata_sourceONjoin_conditionwhen_clause[...] wheredata_sourceis: { [ ONLY ]source_table_name[ * ] | (source_query) } [ [ AS ]source_alias] andwhen_clauseis: { WHEN MATCHED [ ANDcondition] THEN {merge_update|merge_delete| DO NOTHING } | WHEN NOT MATCHED [ ANDcondition] THEN {merge_insert| DO NOTHING } } andmerge_insertis: INSERT [(column_name[, ...] )] [ OVERRIDING { SYSTEM | USER } VALUE ] { VALUES ( {expression| DEFAULT } [, ...] ) | DEFAULT VALUES } andmerge_updateis: UPDATE SET {column_name= {expression| DEFAULT } | (column_name[, ...] ) = [ ROW ] ( {expression| DEFAULT } [, ...] ) | (column_name[, ...] ) = (sub-SELECT) } [, ...] andmerge_deleteis: DELETE
Description
MERGE performs actions that modify rows in the target table identified as target_table_name, using the data_source. MERGE provides a single SQL statement that can conditionally INSERT, UPDATE or DELETE rows, a task that would otherwise require multiple procedural language statements.
First, the MERGE command performs a join from data_source to the target table producing zero or more candidate change rows. For each candidate change row, the status of MATCHED or NOT MATCHED is set just once, after which WHEN clauses are evaluated in the order specified. For each candidate change row, the first clause to evaluate as true is executed. No more than one WHEN clause is executed for any candidate change row.
MERGE actions have the same effect as regular UPDATE, INSERT, or DELETE commands of the same names. The syntax of those commands is different, notably that there is no WHERE clause and no table name is specified. All actions refer to the target table, though modifications to other tables may be made using triggers.
When DO NOTHING is specified, the source row is skipped. Since actions are evaluated in their specified order, DO NOTHING can be handy to skip non-interesting source rows before more fine-grained handling.
There is no separate MERGE privilege. If you specify an update action, you must have the UPDATE privilege on the column(s) of the target table that are referred to in the SET clause. If you specify an insert action, you must have the INSERT privilege on the target table. If you specify a delete action, you must have the DELETE privilege on the target table. If you specify a DO NOTHING action, you must have the SELECT privilege on at least one column of the target table. You will also need SELECT privilege on any column(s) of the data_source and of the target table referred to in any condition (including join_condition) or expression. Privileges are tested once at statement start and are checked whether or not particular WHEN clauses are executed.
MERGE is not supported if the target table is a materialized view, foreign table, or if it has any rules defined on it.
Parameters
with_queryThe
WITHclause allows you to specify one or more subqueries that can be referenced by name in theMERGEquery. See Section 7.8 and SELECT for details. Note thatWITH RECURSIVEis not supported byMERGE.target_table_nameThe name (optionally schema-qualified) of the target table to merge into. If
ONLYis specified before the table name, matching rows are updated or deleted in the named table only. IfONLYis not specified, matching rows are also updated or deleted in any tables inheriting from the named table. Optionally,*can be specified after the table name to explicitly indicate that descendant tables are included. TheONLYkeyword and*option do not affect insert actions, which always insert into the named table only.target_aliasA substitute name for the target table. When an alias is provided, it completely hides the actual name of the table. For example, given
MERGE INTO foo AS f, the remainder of theMERGEstatement must refer to this table asfnotfoo.source_table_nameThe name (optionally schema-qualified) of the source table, view, or transition table. If
ONLYis specified before the table name, matching rows are included from the named table only. IfONLYis not specified, matching rows are also included from any tables inheriting from the named table. Optionally,*can be specified after the table name to explicitly indicate that descendant tables are included.source_queryA query (
SELECTstatement orVALUESstatement) that supplies the rows to be merged into the target table. Refer to the SELECT statement or VALUES statement for a description of the syntax.source_aliasA substitute name for the data source. When an alias is provided, it completely hides the actual name of the table or the fact that a query was issued.
join_conditionjoin_conditionis an expression resulting in a value of typeboolean(similar to aWHEREclause) that specifies which rows in thedata_sourcematch rows in the target table.Warning
Only columns from the target table that attempt to match
data_sourcerows should appear injoin_condition.join_conditionsubexpressions that only reference the target table's columns can affect which action is taken, often in surprising ways.when_clauseAt least one
WHENclause is required.If the
WHENclause specifiesWHEN MATCHEDand the candidate change row matches a row in the target table, theWHENclause is executed if theconditionis absent or it evaluates totrue.Conversely, if the
WHENclause specifiesWHEN NOT MATCHEDand the candidate change row does not match a row in the target table, theWHENclause is executed if theconditionis absent or it evaluates totrue.conditionAn expression that returns a value of type
boolean. If this expression for aWHENclause returnstrue, then the action for that clause is executed for that row.A condition on a
WHEN MATCHEDclause can refer to columns in both the source and the target relations. A condition on aWHEN NOT MATCHEDclause can only refer to columns from the source relation, since by definition there is no matching target row. Only the system attributes from the target table are accessible.merge_insertThe specification of an
INSERTaction that inserts one row into the target table. The target column names can be listed in any order. If no list of column names is given at all, the default is all the columns of the table in their declared order.Each column not present in the explicit or implicit column list will be filled with a default value, either its declared default value or null if there is none.
If the target table is a partitioned table, each row is routed to the appropriate partition and inserted into it. If the target table is a partition, an error will occur if any input row violates the partition constraint.
Column names may not be specified more than once.
INSERTactions cannot contain sub-selects.Only one
VALUESclause can be specified. TheVALUESclause can only refer to columns from the source relation, since by definition there is no matching target row.merge_updateThe specification of an
UPDATEaction that updates the current row of the target table. Column names may not be specified more than once.Neither a table name nor a
WHEREclause are allowed.merge_deleteSpecifies a
DELETEaction that deletes the current row of the target table. Do not include the table name or any other clauses, as you would normally do with a DELETE command.column_nameThe name of a column in the target table. The column name can be qualified with a subfield name or array subscript, if needed. (Inserting into only some fields of a composite column leaves the other fields null.) Do not include the table's name in the specification of a target column.
OVERRIDING SYSTEM VALUEWithout this clause, it is an error to specify an explicit value (other than
DEFAULT) for an identity column defined asGENERATED ALWAYS. This clause overrides that restriction.OVERRIDING USER VALUEIf this clause is specified, then any values supplied for identity columns defined as
GENERATED BY DEFAULTare ignored and the default sequence-generated values are applied.DEFAULT VALUESAll columns will be filled with their default values. (An
OVERRIDINGclause is not permitted in this form.)expressionAn expression to assign to the column. If used in a
WHEN MATCHEDclause, the expression can use values from the original row in the target table, and values from thedata_sourcerow. If used in aWHEN NOT MATCHEDclause, the expression can use values from thedata_sourcerow.DEFAULTSet the column to its default value (which will be
NULLif no specific default expression has been assigned to it).sub-SELECTA
SELECTsub-query that produces as many output columns as are listed in the parenthesized column list preceding it. The sub-query must yield no more than one row when executed. If it yields one row, its column values are assigned to the target columns; if it yields no rows, NULL values are assigned to the target columns. The sub-query can refer to values from the original row in the target table, and values from thedata_sourcerow.
Outputs
On successful completion, a MERGE command returns a command tag of the form
MERGE total_count
The total_count is the total number of rows changed (whether inserted, updated, or deleted). If total_count is 0, no rows were changed in any way.
Notes
The following steps take place during the execution of MERGE.
Perform any
BEFORE STATEMENTtriggers for all actions specified, whether or not theirWHENclauses match.Perform a join from source to target table. The resulting query will be optimized normally and will produce a set of candidate change rows. For each candidate change row,
Evaluate whether each row is
MATCHEDorNOT MATCHED.Test each
WHENcondition in the order specified until one returns true.When a condition returns true, perform the following actions:
Perform any
BEFORE ROWtriggers that fire for the action's event type.Perform the specified action, invoking any check constraints on the target table.
Perform any
AFTER ROWtriggers that fire for the action's event type.
Perform any
AFTER STATEMENTtriggers for actions specified, whether or not they actually occur. This is similar to the behavior of anUPDATEstatement that modifies no rows.
In summary, statement triggers for an event type (say, INSERT) will be fired whenever we specify an action of that kind. In contrast, row-level triggers will fire only for the specific event type being executed. So a MERGE command might fire statement triggers for both UPDATE and INSERT, even though only UPDATE row triggers were fired.
You should ensure that the join produces at most one candidate change row for each target row. In other words, a target row shouldn't join to more than one data source row. If it does, then only one of the candidate change rows will be used to modify the target row; later attempts to modify the row will cause an error. This can also occur if row triggers make changes to the target table and the rows so modified are then subsequently also modified by MERGE. If the repeated action is an INSERT, this will cause a uniqueness violation, while a repeated UPDATE or DELETE will cause a cardinality violation; the latter behavior is required by the SQL standard. This differs from historical PostgreSQL behavior of joins in UPDATE and DELETE statements where second and subsequent attempts to modify the same row are simply ignored.
If a WHEN clause omits an AND sub-clause, it becomes the final reachable clause of that kind (MATCHED or NOT MATCHED). If a later WHEN clause of that kind is specified it would be provably unreachable and an error is raised. If no final reachable clause is specified of either kind, it is possible that no action will be taken for a candidate change row.
The order in which rows are generated from the data source is indeterminate by default. A source_query can be used to specify a consistent ordering, if required, which might be needed to avoid deadlocks between concurrent transactions.
There is no RETURNING clause with MERGE. Actions of INSERT, UPDATE and DELETE cannot contain RETURNING or WITH clauses.
When MERGE is run concurrently with other commands that modify the target table, the usual transaction isolation rules apply; see Section 13.2 for an explanation on the behavior at each isolation level. You may also wish to consider using INSERT ... ON CONFLICT as an alternative statement which offers the ability to run an UPDATE if a concurrent INSERT occurs. There are a variety of differences and restrictions between the two statement types and they are not interchangeable.
Examples
Perform maintenance on customer_accounts based upon new 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);
Notice that this would be exactly equivalent to the following statement because the MATCHED result does not change during execution.
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);
Attempt to insert a new stock item along with the quantity of stock. If the item already exists, instead update the stock count of the existing item. Don't allow entries that have zero stock.
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;
The wine_stock_changes table might be, for example, a temporary table recently loaded into the database.
Compatibility
This command conforms to the SQL standard.
The WITH clause and DO NOTHING action are extensions to the SQL standard.