Merge (SQL)

Merge — оператор языка SQL, который позволяет слить данные одной таблицы с данными другой таблицы. При слиянии таблиц проверяется условие, и если оно истинно, то выполняется Update, а если нет — Insert. Причём нельзя изменять поля таблицы в секции Update, по которым идет связывание двух таблиц. Данные изменяются или добавляются только для таблицы в предложении MERGE INTO, таблица в предложении USING остается без изменений.

Оператор был официально представлен в стандарте SQL:2003 и расширен в стандарте SQL: 2008^{[источник не указан 551 день]}.

В типичном решении для хранилища данных SQL часто важно поддерживать историю данных в хранилище со ссылкой на исходные данные, которые передаются инструменту ETL. Наиболее распространенный вариант использования — попытка поддерживать медленно меняющиеся измерения (SCD) в хранилище данных. В таких случаях необходимо вставить новые записи в хранилище данных, удалить или пометить записи из хранилища, которых больше нет в источнике, и обновить данные в хранилище, которые были обновлены в источнике^[1].

Оператор SQL MERGE был представлен в релизе SQL Server 2008, что дало программистам баз данных большую гибкость, чтобы упростить их беспорядочный код в работе операторов INSERT, UPDATE и DELETE, применяя логику для реализации SCD в ETL^[2].

Есть несколько аспектов, с помощью которых возможно оптимизировать производительность операторов MERGE. Появилась возможность записи операторов DML (INSERT, UPDATE и DELETE), объединенных в один оператор. С точки зрения обработки данных это полезно, так как сокращает операции ввода-вывода с диска для каждого из трех операторов в отдельности, и даёт возможность данным считываться только один раз^[3].

Кроме того, производительность оператора MERGE сильно зависит от индексов, используемых для сопоставления как исходной, так и целевой таблиц. Помимо индексов, также важно оптимизировать условия объединения. При этом должна быть возможность отфильтровать исходную таблицу, чтобы оператор извлекал только необходимые записи для выполнения необходимых операций^[2].

-- SQL Server and Azure SQL Database
[ WITH <common_table_expression> [,...n] ]  
MERGE
    [ TOP ( expression ) [ PERCENT ] ]
    [ INTO ] <target_table> [ WITH ( <merge_hint> ) ] [ [ AS ] table_alias ]  
    USING <table_source> [ [ AS ] table_alias ]
    ON <merge_search_condition>  
    [ WHEN MATCHED [ AND <clause_search_condition> ]  
        THEN <merge_matched> ] [ ...n ]  
    [ WHEN NOT MATCHED [ BY TARGET ] [ AND <clause_search_condition> ]  
        THEN <merge_not_matched> ]  
    [ WHEN NOT MATCHED BY SOURCE [ AND <clause_search_condition> ]  
        THEN <merge_matched> ] [ ...n ]  
    [ <output_clause> ]  
    [ OPTION ( <query_hint> [ ,...n ] ) ]
;

<target_table> ::=  
{
    [ database_name . schema_name . | schema_name . ]  
  target_table  
}

<merge_hint>::=  
{  
    { [ <table_hint_limited> [ ,...n ] ]  
    [ [ , ] INDEX ( index_val [ ,...n ] ) ] }  
}

<merge_search_condition> ::=  
    <search_condition>

<merge_matched>::=  
    { UPDATE SET <set_clause> | DELETE }

<merge_not_matched>::=  
{  
    INSERT [ ( column_list ) ]
        { VALUES ( values_list )  
        | DEFAULT VALUES }  
}

<clause_search_condition> ::=  
    <search_condition>

при этом:

• каждый оператор MERGE должен заканчиваться точкой с запятой. Если в конце оператора MERGE нет точки с запятой, будет выдана ошибка;
• можно использовать SELECT @@RowCount после написания оператора MERGE, который вернет количество записей, измененных транзакцией;
• для работы оператора MERGE обязательно наличие одного из предложений MATCHED^[3].

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

Указывает количество или процент затронутых строк. expression может быть либо числом, либо процентом от числа строк. Строки, на которые ссылается выражение TOP, не расположены в определенном порядке.

Имя базы данных, в которой расположена таблица target_table.

Имя схемы, к которой принадлежит таблица target_table.

Таблица или представление, с которыми выполняется сопоставление строк данных из таблицы <table_source> по условию <clause_search_condition>. Таблица target_table является целевым объектом любых операций вставки, обновления или удаления, указанных предложениями WHEN в инструкции MERGE. target_table не может быть удаленно расположенной таблицей. Для таблицы target_table не должно существовать определенных правил.

Указания можно задать как <merge_hint>.

Альтернативное имя для ссылок на таблицу для target_table.

Указывает источник данных, который сопоставляется со строками данных в таблице target_table на основе условия <merge_search condition>. Результат этого совпадения обуславливает действия, которые выполняются предложениями WHEN инструкции MERGE. Аргумент <table_source> может быть удаленной таблицей или производной таблицей, которая обращается к удаленным таблицам.

Альтернативное имя для ссылок на таблицу для table_source.

Указывает условия, по которым <table_source> соединяется с таблицей target_table для сопоставления. Необходимо указать столбцы целевой таблицы, которые сравниваются с соответствующим столбцом исходной таблицы.

Указывает, что все строки *target_table, которые соответствуют строкам, возвращенным выражением <table_source> ON <merge_search_condition>, и удовлетворяют дополнительным условиям поиска, обновляются или удаляются в соответствии с предложением <merge_matched>.

Инструкция MERGE включать не больше двух предложений WHEN MATCHED. Если указаны два предложения, первое предложение должно сопровождаться предложением AND <search_condition>.

Указывает, что в таблицу target_table вставляется строка для каждой строки, возвращенной выражением <table_source> ON <merge_search_condition>, которая не соответствует строке в таблице target_table, но удовлетворяет дополнительному условию поиска (если оно есть). Значения для вставки указываются с помощью предложения <merge_not_matched>. Инструкция MERGE может иметь только одно предложение WHEN NOT MATCHED [ BY TARGET ].

Указывает, что все строки *target_table, которые не соответствуют строкам, возвращенным выражением <table_source> ON <merge_search_condition>, и удовлетворяют дополнительным условиям поиска, обновляются или удаляются в соответствии с предложением <merge_matched>.

Указывается любое действительное условие поиска.

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

Использование ключевых слов NOLOCK и READUNCOMMITTED запрещено.

Указывает имя или идентификатор одного или нескольких индексов целевой таблицы для выполнения неявного соединения с исходной таблицей.

Возвращает по одной строке для каждой строки в таблице target_table, в которой выполнена операция обновления, вставки или удаления, без какого-либо определенного порядка. Параметр $action может быть указан в предложении вывода. $action — это столбец типа nvarchar(10), который возвращает одно из трех значений для каждой строки: INSERT, UPDATE или DELETE — согласно действию, которое было выполнено с этой строкой. Предложение OUTPUT рекомендуется использовать для запроса или подсчета строк, на которые влияет предложение MERGE.

Указывает, что для настройки способа, которым компонент Database Engine обрабатывает инструкцию, используются подсказки оптимизатора.

Указывает действие обновления или удаления, применяемое ко всем строкам таблицы target_table, которые не соответствуют строкам, возвращенным выражением <table_source> ON <merge_search_condition>, и удовлетворяют дополнительным условиям поиска.

Указывает список имен столбцов или переменных, которые необходимо обновить в целевой таблице, и значений для их обновления.

Указывает, что строки, совпадающие со строками в target_table, удаляются.

Указываются значения для вставки в целевую таблицу.

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

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

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

Задает условия поиска для указания <merge_search_condition> или <clause_search_condition>.

Определяет шаблон сопоставления графов.

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

На все операции удаления, вставки или обновления, применяемые инструкцией MERGE к целевой таблице, распространяются все ограничения, определенные для этой таблицы, включая все каскадные ограничения целостности данных. Если IGNORE_DUP_KEY имеет значение ON для любого из уникальных индексов целевой таблицы, то инструкция MERGE игнорирует этот параметр.

Чтобы использовать инструкцию MERGE, необходима точка с запятой (;) как признак конца инструкции. Возникает ошибка 10713, если инструкция MERGE выполняется без признака конца конструкции.

 MERGE INTO table_name USING table_reference ON (condition)
   WHEN MATCHED THEN
   UPDATE SET column1 = value1 [, column2 = value2 …]
   WHEN NOT MATCHED THEN
   INSERT (column1 [, column2 …]) VALUES (value1 [, value2 …]);

Данный оператор реализован в следующих системах управления базами данных Oracle Database, IBM Db2, Teradata, EXASOL, Firebird, CUBRID, H2, HSQLDB, MS SQL, Vectorwise, Apache Derby и PostgreSQL (с 15ой версии).

Также оператор используется в базе данных Microsoft Azure SQL.

• Документация Oracle 12c
• Документация SQL Server 2008

Источник: https://web.archive.org/web/20111120170710/http://oracle-wiki.ru/wiki/Merge