Merge (SQL)

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

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

Применение оператора SQL MERGE

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

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

Оптимизация производительности оператора SQL MERGE

Есть несколько аспектов, с помощью которых возможно оптимизировать производительность операторов 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].

Аргументы

WITH <common_table_expression>

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

TOP (expression) [ PERCENT ]

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

database_name

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

schema_name

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

target_table

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

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

[ AS ] table_alias

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

USING <table_source>

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

[ AS ] table_alias

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

ON <merge_search_condition>

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

WHEN MATCHED THEN <merge_matched>

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

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

WHEN NOT MATCHED [ BY TARGET ] THEN <merge_not_matched>

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

WHEN NOT MATCHED BY SOURCE THEN <merge_matched>

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

AND <clause_search_condition>

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

<table_hint_limited>

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

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

INDEX (index_val [ ,…n ])

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

<output_clause>

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

OPTION (<query_hint> [ ,…n ])

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

<merge_matched>

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

UPDATE SET <set_clause>

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

DELETE

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

<merge_not_matched>

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

(column_list)

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

VALUES (values_list)

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

DEFAULT VALUES

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

<search_condition>

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

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

Remarks

Должно быть указано по крайней мере одно из трех предложений 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.

Примечания

↑ SQL Server MERGE to insert, update and delete at the same time (англ.). www.mssqltips.com. Дата обращения: 22 сентября 2022. Архивировано 22 сентября 2022 года.
↑ ¹ ² Aveek Das. Understanding the SQL MERGE statement (амер. англ.). SQL Shack - articles about database auditing, server performance, data recovery, and more (27 июля 2020). Дата обращения: 22 сентября 2022. Архивировано 22 сентября 2022 года.
↑ ¹ ² mstehrani. MERGE (Transact-SQL) - SQL Server (амер. англ.). learn.microsoft.com. Дата обращения: 22 сентября 2022.

Ссылки

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

[1] SQL Server MERGE to insert, update and delete at the same time (англ.). www.mssqltips.com. Дата обращения: 22 сентября 2022. Архивировано 22 сентября 2022 года.

[:0-2] ¹ ² Aveek Das. Understanding the SQL MERGE statement (амер. англ.). SQL Shack - articles about database auditing, server performance, data recovery, and more (27 июля 2020). Дата обращения: 22 сентября 2022. Архивировано 22 сентября 2022 года.

[:1-3] ¹ ² mstehrani. MERGE (Transact-SQL) - SQL Server (амер. англ.). learn.microsoft.com. Дата обращения: 22 сентября 2022.

[1]

[2]

[3]