MERGE#

MERGE
MERGE
Назад НаверхSQL КомандыНачало Далее

MERGE

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

Синтаксис

[ WITH with_query [, ...] ]
MERGE INTO [ ONLY ] target_table_name [ * ] [ [ AS ] target_alias ]
USING data_source ON join_condition
when_clause [...]

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 [ 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 [, ...] ) = ( { expression | DEFAULT } [, ...] ) } [, ...]

and merge_delete is:

DELETE

Описание

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

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

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

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

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

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

Параметры

target_table_name

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

target_alias

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

source_table_name

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

source_query

Запрос (SELECT или VALUES) который предоставляет строки для объединения в target_table_name. См. SELECT или VALUES для описания синтаксиса.

source_alias

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

join_condition

join_condition - это выражение, результатом которого является значение типа boolean (аналогично предложению WHERE), которое указывает, какие строки в data_source соответствуют строкам в target_table_name.

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

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

when_clause

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

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

И наоборот, если в предложении WHEN указано WHEN NOT MATCHED и кандидат на изменение не соответствует строке в target_table_name, то предложение WHEN выполняется, если condition отсутствует или оно оценивается как true.

condition

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

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

merge_insert

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

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

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

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

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

merge_update

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

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

merge_delete

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

column_name

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

OVERRIDING SYSTEM VALUE

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

OVERRIDING USER VALUE

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

DEFAULT VALUES

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

expression

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

DEFAULT

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

with_query

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

Выводы

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

MERGE total_count

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

Примечания

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

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

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

    1. Оцените, является ли каждая строка MATCHED или NOT MATCHED.

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

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

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

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

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

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

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

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

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

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

В операторе 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.

Назад Наверх Далее
LOCK Начало MOVE