Операция MERGE в языке Transact-SQL – описание и примеры

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

Начнем мы, конечно же, с небольшой теории.

Заметка! Начинающим рекомендую посмотреть мой видеокурс по T-SQL.

Что такое MERGE в T-SQL?

MERGE – операция в языке T-SQL, при которой происходит обновление, вставка или удаление данных в таблице на основе результатов соединения с данными другой таблицы или SQL запроса. Другими словами, с помощью MERGE можно осуществить слияние двух таблиц, т.е. синхронизировать их.

В операции MERGE происходит объединение по ключевому полю или полям основной таблицы (в которой и будут происходить все изменения) с соответствующими полями другой таблицы или результата запроса. В итоге если условие, по которому происходит объединение, истина (WHEN MATCHED), то мы можем выполнить операции обновления или удаления, если условие не истина, т.е. отсутствуют данные (WHEN NOT MATCHED), то мы можем выполнить операцию вставки (INSERT добавление данных), также если в основной таблице присутствуют данные, которое отсутствуют в таблице (или результате запроса) источника (WHEN NOT MATCHED BY SOURCE), то мы можем выполнить обновление или удаление таких данных.

В дополнение к основным перечисленным выше условиям можно указывать «Дополнительные условия поиска», они указываются через ключевое слово AND.

Упрощённый синтаксис MERGE

Важные моменты при использовании MERGE:

• В конце инструкции MERGE обязательно должна идти точка с запятой (;) иначе возникнет ошибка;
• Должно быть, по крайней мере, одно условие MATCHED;
• Операцию MERGE можно использовать совместно с CTE (обобщенным табличным выражением);
• В инструкции MERGE можно использовать ключевое слово OUTPUT, для того чтобы посмотреть какие изменения были внесены. Для идентификации операции здесь в OUTPUT можно использовать переменную $action;
• На все операции к основной таблице, которые предусмотрены в MERGE (удаления, вставки или обновления), действуют все ограничения, определенные для этой таблицы;
• Функция @@ROWCOUNT, если ее использовать после инструкции MERGE, будет возвращать общее количество вставленных, обновленных и удаленных строк;
• Для того чтобы использовать MERGE необходимо разрешение на INSERT, UPDATE или DELETE в основной таблице, и разрешение SELECT для таблицы источника;
• При использовании MERGE необходимо учитывать, что все триггеры AFTER на INSERT, UPDATE или DELETE, определенные для целевой таблицы, будут запускаться.

А теперь переходим к практике. И для начала давайте определимся с исходными данными.

Исходные данные для примеров операции MERGE

У меня в качестве SQL сервера будет выступать Microsoft SQL Server 2016 Express. На нем есть тестовая база данных, в которой я создаю тестовые таблицы, например, с товарами: TestTable – это у нас будет целевая таблица, т.е. та над которой мы будем производить все изменения, и TestTableDop – это таблица источник, т.е. данные в соответствии с чем, мы будем производить изменения.

Запрос для создания таблиц.

Далее я их наполняю тестовыми данными.

Посмотрим на эти данные.

Видно, что в целевой таблице значение поля Summa = 0, а также есть несоответствие некоторых идентификаторов, т.е. у нас есть товары, которые есть в одной таблице, при этом они отсутствуют в другой.

Пример 1 – обновление и добавление данных с помощью MERGE

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

Мы видим, что у нас было две операции UPDATE и одна INSERT. Так оно и есть, две строки из таблицы TestTable соответствуют двум строкам в таблице TestTableDop, т.е. у них один и тот же ProductId, у данных строк в таблице TestTable мы обновили поля ProductName и Summa. При этом в таблице TestTableDop есть строка, которая отсутствует в TestTable, поэтому мы ее и добавили через INSERT.

Пример 2 – синхронизация таблиц с помощью MERGE

Теперь, допустим, нам нужно синхронизировать таблицу TestTable с таблицей TestTableDop, для этого мы добавим еще одно условие WHEN NOT MATCHED BY SOURCE, суть его в том, что мы удалим строки, которые есть в TestTable, но нет в TestTableDOP. Но для начала, для того чтобы у нас все три условия отработали (в частности WHEN NOT MATCHED) давайте в таблице TestTable удалим строку, которую мы добавили в предыдущем примере. Также здесь я в качестве источника укажу запрос, чтобы Вы видели, как можно использовать запросы в качестве источника.

В итоге мы видим, что у нас таблицы содержат одинаковые данные. Для этого мы выполнили две операции UPDATE, одну INSERT и одну DELETE. При этом мы использовали всего одну инструкцию MERGE.

Пример 3 – операция MERGE с дополнительным условием

Сейчас давайте выполним запрос похожий на запрос, который мы использовали в примере 1, только добавим дополнительное условие на обновление данных, например, мы будем обновлять TestTable только в том случае, если поле Summa, в TestTableDop, содержит какие-нибудь данные (например, мы не хотим использовать некорректные значения для обновления). Для того чтобы было видно, как отработало это условие, давайте предварительно очистим у одной строки в таблице TestTableDop поле Summa (поставим NULL).

В итоге у меня обновилось всего две строки, притом, что все три строки успешно выполнили условие объединения, но одна строка не обновилась, так как сработало дополнительное условие Summa IS NOT NULL, потому что поле Summa у строки с ProductId = 2, в таблице TestTableDop, не содержит никаких данных, т.е. NULL.

Заметка! Для комплексного изучения языка SQL и T-SQL рекомендую посмотреть мои видеокурсы по T-SQL, которые помогут Вам «с нуля» научиться работать с SQL и программировать на T-SQL в Microsoft SQL Server.

Merge (SQL)

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

Оператор был официально включен в стандарт SQL:2008.

Пример

Ссылки

• Ключевые слова SQL

Wikimedia Foundation . 2010 .

Полезное

Смотреть что такое «Merge (SQL)» в других словарях:

Merge (SQL) — A relational database management system uses SQL MERGE (upsert) statements to INSERT new records or UPDATE existing records depending on whether or not a condition matches. It was officially introduced in the SQL:2008 standard. Contents 1 Usage 2 … Wikipedia

Merge — See Help:Merging for the usage of Merge in Wikipedia. Contents 1 Concepts 2 Computer science 3 Music … Wikipedia

SQL — Класс языка: Мультипарадигмальный Появился в: 1974 Автор(ы): Дональд Чэмбэрлин Рэймонд Бойс Релиз: SQL:2008 (2008) Типизация данных … Википедия

SQL Server Integration Services — (SSIS) is a component of Microsoft SQL Server 2005 and 2008. It replaces Data Transformation Services, which has been a feature of SQL Server since Version 7.0. Unlike DTS, which was included in all versions, SSIS is available in the Standard ,… … Wikipedia

SQL Server Compact — Filename extension .sdf Developed by Microsoft Type of format Relational database Microsoft SQL Server Compact (SQL CE) is a compact relational database produced by Microsoft for applications that run on mobile devices and desktops. Prior to the… … Wikipedia

SQL-92 — SQL 92  третья версия языка запросов к базам данных SQL. В отличие от стандарта SQL 89, третья версия стала весьма существенным обновлением языка. За исключением некоторых мелких противоречий стандарт SQL 89 практически полностью совместим… … Википедия

SQL:2008 — SQL:2008  шестая версия (ревизия) языка запросов баз данных SQL. Содержание 1 Общие сведения 2 Новшества 3 … Википедия

SQL:2003 — Saltar a navegación, búsqueda SQL: 2003 es la quinta revisión del lenguaje de consulta de base de datos SQL. La última revisión de el estándar es SQL:2008. Resumen El estándar SQL:2003 hace pequeñas modificaciones sobre la versión anterior,… … Wikipedia Español

SQL — This article is about the database language. For the airport with IATA code SQL, see San Carlos Airport. SQL Paradigm(s) Multi paradigm Appeared in 1974 Designed by Donald D. Chamberlin Raymond F. Boyce Developer … Wikipedia

SQL — Structured Query Language SQL Apparu en 1974 Auteur … Wikipédia en Français

Инструкция MERGE

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

Выполняется операция UPDATE , если строки существуют, и операция INSERT , если это новая строка:

исключает необходимость в отдельных обновлениях;

повышается производительность и простота использования;

удобна в приложениях хранилища данных.

Сервером Oracle поддерживается инструкция MERGE для операций INSERT , UPDATE и DELETE . Используя эту инструкцию, можно обновить, вставить или удалить строку по условию в таблице, таким образом исключая необходимость применения нескольких инструкций DML. Решение о выполнении обновления, вставки или удаления в целевой таблице основывается на условии в предложении ON .

Необходимо иметь объектные привилегии INSERT и UPDATE на целевую таблицу и объектную привилегию SELECT на исходную таблицу. Чтобы задать предложение DELETE для merge_update_clause , необходимо также обладать объектной привилегией DELETE на целевую таблицу.

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

Альтернативный подход состоит в использовании циклов PL/SQL и нескольких инструкций DML. Однако инструкцию MERGE удобно использовать и проще выразить в виде одиночной инструкции SQL.

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

Синтаксис инструкции MERGE

Используя инструкцию MERGE , можно по определенному условию вставлять, обновлять и удалять строки в таблице.

Объединение строк

Используя инструкцию MERGE , можно обновлять существующие строки и вставлять новые строки по определенному условию. Применяя инструкцию MERGE , можно удалить устаревшие строки одновременно с обновлением строк в таблице. Чтобы сделать это, в синтаксис инструк- ции MERGE включите предложение DELETE со своим собственным предложением WHERE .

Предложение INTO — задает целевую таблицу, которая обновляется или в которую выполняется вставка.

Предложение USING — идентифицирует источник обновляемых или вставляемых данных; может быть таблицей, представлением или подзапросом.

Предложение ON — условие, по которому операция MERGE выполняет обновление или вставку.

WHEN MATCHED | WHEN NOT MATCHED — предписывает серверу, как реагировать на результаты условия объединения

Более подробно изложено в документации Oracle Database 11g SQL Reference (Справочник по SQL для базы данных Oracle 11g).

/привет/мир/etc

Команда MERGE появилась в версии 9 СУБД Oracle и была усовершенствована в версии 10. Команда не делает ничего такого, чего нельзя сделать с помощью традиционных UPDATE , INSERT (и DELETE ), но в ряде случаев делает это эффективнее.

Синтаксис команды MERGE :

Команда изменяет таблицу target_table , используя в качестве источника данных таблицу, вью или подзапрос из части USING . Часть ON condition сопоставляет строки источника со строками целевой таблицы. При наличии в target_table строк, соответствующих источнику, выполняется часть WHEN MATCHED THEN UPDATE , иначе — часть WHEN NOT MATCHED THEN INSERT . Иными словами, строки, соответствующие источнику, модифицируются, а отсутствующие — добавляются.

Опциональные where_clause в частях UPDATE и INSERT ограничивают модифицируемые и добавляемые строки. Опция DELETE позволяет удалять строки, модифицированные в части UPDATE .

Что делает и чего не делает MERGE

Чтобы понять, как работает MERGE , поэкспериментируем. Вначале я создам таблицу — источник данных:

Теперь создам таблицу books_copy , назначение которой — содержать точную копию данных из таблицы books :

Сделаю изменения в таблице-источнике, удалив, изменив и добавив по одной строке. После этого таблица books_copy уже не содержит актуальной копии и нуждается в обновлении:

Теперь для того, чтобы привести books_copy в соответствие с books необходимо:

1. модифицировать строки books_copy , такие, что books_copy.book_id = books.book_id
2. добавить в books_copy строки из books c books_id , отсутствующими в books_copy
3. удалить из books_copy строки c books_id , отсутствующими в books

Команда MERGE модифицирует и добавит строки в books_copy (пп. 1 и 2):

Oracle сообщает о двух обработанных строках: одна из них модифицирована, другая — добавлена. Результат:

Однако, MERGE не может удалить из целевой таблицы строки, которые отсутствуют в источнике (п.3)!

Сделаю это отдельной командой DELETE и проверю, что теперь содержимое таблиц одинаково:

Что происходит «за кулисами»

Оказывается, для того, чтобы получить исходные данные для операций UPDATE и INSERT , команда MERGE выполняет внешнее соединение источника и целевой таблицы: source LEFT OUTER JOIN target . Результатом являются

1. строки, которые есть и в источнике и в приемнике, и
2. строки, которые есть только в источнике, но отсутствуют в приемнике.

А для того, чтобы в одном запросе выбрать вставляемые, модифицируемые, а также удаляемые строки, нужно воспользоваться полным внешним соединением, source FULL OUTER JOIN target :

К сожалению, MERGE этого не делает (и потому называется MERGE, а не SYNC).

Не нужно модифицировать все строки

Модификация всех строк целевой таблицы, сопоставленных с источником, в общем случае не нужна и затратна. Однако, в рассмотренном примере происходит именно это. Если работать таблицей, содержащей миллионы строк, то СУБД будет делать много ненужной работы.

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

Итак, последние изменения в books сделаны 2 суток назад. Приведем в соответствие books_copy и books , после чего сделаем новые изменения в источнике:

Обновим books_copy , модифицируя только строки, источник которых был изменен в течение последних суток:

А можно ли добиться аналогичного результата иначе, изменив условие ON ? Оказывается, нельзя:

Проверка условия ON определяет, какая из операций, UPDATE или INSERT , будет выполнена. И в данном случае часть WHEN NOT MATCHED THEN INSERT пытается добавить в books_copy все записи из books , которые не соответствуют условию ON ! Что приводит к нарушению первичного ключа.

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

Иногда полезно модифицировать все строки?

Опция DELETE в MERGE работает только для строк, модифицированных в части UPDATE . Я не нашел готового ответа на вопрос, в каких случаях полезна опция DELETE , но представил себе следующиую ситуацию.

Иногда из целевой таблицы нужно удалять строки по прошествии времени или по наступлении события. Чтобы команда MERGE смогла удалять строки, ставшие ненужными, все сопоставленные с источником строки должны модифицироваться частью UPDATE .

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

Что мы имеем на данный момент?

Согласно предложенной выше логике, после выполнения команды MERGE с DELETE в books_copy должны произойти следующие изменения:

1. будет модифицирована строка с book_id = 3 ,
2. будет (модифицирована и) удалена строка с book_id = 2 , поскольку соответствующая строка источника была обновлена ранее вчерашней полночи,
3. будет добавлена строка с book_id = 4 .

В заключение, удаляю использованные в экспериментах таблицы:

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