MERGE#
MERGE
MERGE — условно вставлять, обновлять или удалять строки таблицы
Синтаксис
[ WITHwith_query
[, ...] ] MERGE INTO [ ONLY ]target_table_name
[ * ] [ [ AS ]target_alias
] USINGdata_source
ONjoin_condition
when_clause
[...] [ RETURNING { * |output_expression
[ [ AS ]output_name
] } [, ...] ] wheredata_source
is: { [ ONLY ]source_table_name
[ * ] | (source_query
) } [ [ AS ]source_alias
] andwhen_clause
is: { WHEN MATCHED [ ANDcondition
] THEN {merge_update
|merge_delete
| DO NOTHING } | WHEN NOT MATCHED BY SOURCE [ ANDcondition
] THEN {merge_update
|merge_delete
| DO NOTHING } | WHEN NOT MATCHED [ BY TARGET ] [ ANDcondition
] THEN {merge_insert
| DO NOTHING } } andmerge_insert
is: INSERT [(column_name
[, ...] )] [ OVERRIDING { SYSTEM | USER } VALUE ] { VALUES ( {expression
| DEFAULT } [, ...] ) | DEFAULT VALUES } andmerge_update
is: UPDATE SET {column_name
= {expression
| DEFAULT } | (column_name
[, ...] ) = [ ROW ] ( {expression
| DEFAULT } [, ...] ) | (column_name
[, ...] ) = (sub-SELECT
) } [, ...] andmerge_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
.
Выполните все триггеры
BEFORE STATEMENT
для всех действий, указанных, независимо от того, соответствуют ли их предложениямWHEN
.Выполните соединение таблицы исходных данных с целевой таблицей. Полученный запрос будет оптимизирован как обычно и будет возвращать набор кандидатов на изменение строк. Для каждой кандидатской строки изменения,
Оцените, соответствует ли каждая строка
MATCHED
,NOT MATCHED BY SOURCE
илиNOT MATCHED [BY TARGET]
.Протестируйте каждое условие
WHEN
в указанном порядке, пока одно из них не вернет значение true.Когда условие возвращает true, выполните следующие действия:
Выполните все триггеры
BEFORE ROW
, которые срабатывают для типа события действия.Выполните указанное действие, вызывая все проверочные ограничения на целевой таблице.
Выполните все триггеры
AFTER ROW
, которые срабатывают для данного типа события.
Если целевое отношение является представлением с
INSTEAD OF ROW
триггерами для типа события действия, они используются для выполнения действия вместо этого.
Выполните все триггеры
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.