Sql merge into: MERGE (Transact-SQL) — SQL Server

Содержание

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

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

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

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

Содержание

Что такое MERGE в T-SQL?
Исходные данные для примеров операции MERGE
Пример 1 – обновление и добавление данных с помощью MERGE
Пример 2 – синхронизация таблиц с помощью MERGE
Пример 3 – операция MERGE с дополнительным условием

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

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

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

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

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

  
        MERGE <Основная таблица>
                USING <Таблица или запрос источника> 
                ON <Условия объединения>
                [ WHEN MATCHED [ AND <Доп. условие> ]
                        THEN <UPDATE или DELETE>
                [ WHEN NOT MATCHED [ AND Доп. условие> ]
                        THEN <INSERT> ]
                [ WHEN NOT MATCHED BY SOURCE [ AND <Доп. условие> ]
                        THEN <UPDATE или DELETE> ] [ ...n ] 
                [ OUTPUT ]
        ;

Важные моменты при использовании 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 – это таблица источник, т.е. данные в соответствии с чем, мы будем производить изменения.

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

  
  --Целевая таблица
  CREATE TABLE dbo.TestTable(
        ProductId INT NOT NULL,
        ProductName VARCHAR(50) NULL,
        Summa MONEY NULL,
  CONSTRAINT PK_TestTable PRIMARY KEY CLUSTERED (ProductId ASC)
  )
  --Таблица источник
  CREATE TABLE dbo.TestTableDop(
        ProductId INT NOT NULL,
        ProductName VARCHAR(50) NULL,
        Summa MONEY NULL,
  CONSTRAINT PK_TestTableDop PRIMARY KEY CLUSTERED (ProductId ASC)
  )

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

   
        --Добавляем данные в основную таблицу
        INSERT INTO dbo.TestTable
                           (ProductId,ProductName,Summa)
                 VALUES
                           (1, 'Компьютер', 0)
        GO
        INSERT INTO dbo. TestTable
                           (ProductId,ProductName,Summa)
                 VALUES
                           (2, 'Принтер', 0)
        GO
        INSERT INTO dbo.TestTable
                           (ProductId,ProductName,Summa)
                 VALUES
        (3, 'Монитор', 0)
        GO
        --Добавляем данные в таблицу источника
        INSERT INTO dbo.TestTableDop
                           (ProductId,ProductName,Summa)
                 VALUES
                           (1, 'Компьютер', 500)
        GO
        INSERT INTO dbo.TestTableDop
                           (ProductId,ProductName,Summa)
                 VALUES
                           (2, 'Принтер', 300)
        GO
        INSERT INTO dbo.TestTableDop
                           (ProductId,ProductName,Summa)
                 VALUES
                           (4, 'Монитор', 400)
        GO

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

  
  SELECT * FROM dbo.TestTable

  SELECT * FROM dbo.TestTableDop

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

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

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

   
        MERGE dbo.TestTable AS T_Base --Целевая таблица
        USING dbo.TestTableDop AS T_Source --Таблица источник
        ON (T_Base.ProductId = T_Source.ProductId) --Условие объединения
        WHEN MATCHED THEN --Если истина (UPDATE)
                 UPDATE SET ProductName = T_Source. ProductName, Summa = T_Source.Summa
        WHEN NOT MATCHED THEN --Если НЕ истина (INSERT)
                 INSERT (ProductId, ProductName, Summa) 
                 VALUES (T_Source.ProductId, T_Source.ProductName, T_Source.Summa)
        --Посмотрим, что мы сделали
        OUTPUT $action AS [Операция], Inserted.ProductId,
                   Inserted.ProductName AS ProductNameNEW,
                   Inserted.Summa AS SummaNEW, 
                   Deleted.ProductName AS ProductNameOLD, 
                   Deleted.Summa AS SummaOLD; --Не забываем про точку с запятой
        --Итоговый результат
        SELECT * FROM dbo.TestTable
        SELECT * FROM dbo.TestTableDop

Мы видим, что у нас было две операции 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 удалим строку, которую мы добавили в предыдущем примере. Также здесь я в качестве источника укажу запрос, чтобы Вы видели, как можно использовать запросы в качестве источника.

   
        --Удаление строки с ProductId = 4 
        --для того чтобы отработало условие WHEN NOT MATCHED
        DELETE dbo.TestTable WHERE ProductId = 4
        --Запрос MERGE для синхронизации таблиц
        MERGE dbo.TestTable AS T_Base --Целевая таблица
        --Запрос в качестве источника
        USING (SELECT ProductId, ProductName, Summa 
                   FROM dbo. TestTableDop) AS T_Source (ProductId, ProductName, Summa) 
        ON (T_Base.ProductId = T_Source.ProductId) --Условие объединения
        WHEN MATCHED THEN --Если истина (UPDATE)
                 UPDATE SET ProductName = T_Source.ProductName, Summa = T_Source.Summa
        WHEN NOT MATCHED THEN --Если НЕ истина (INSERT)
                 INSERT (ProductId, ProductName, Summa)
                 VALUES (T_Source.ProductId, T_Source.ProductName, T_Source.Summa)
         --Удаляем строки, если их нет в TestTableDOP
        WHEN NOT MATCHED BY SOURCE THEN
                 DELETE  
        --Посмотрим, что мы сделали
        OUTPUT $action AS [Операция], Inserted.ProductId, Inserted.ProductName AS ProductNameNEW,
                   Inserted.Summa AS SummaNEW,Deleted.ProductName AS ProductNameOLD, 
                   Deleted.Summa AS SummaOLD; --Не забываем про точку с запятой
        --Итоговый результат
        SELECT * FROM dbo. TestTable
        SELECT * FROM dbo.TestTableDop

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

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

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

   
        --Очищаем поле сумма у одной строки в TestTableDop
        UPDATE dbo. TestTableDop SET Summa = NULL 
        WHERE ProductId = 2
        --Запрос MERGE 
        MERGE dbo. TestTable AS T_Base --Целевая таблица
        USING dbo.TestTableDop AS T_Source --Таблица источник
        ON (T_Base.ProductId = T_Source.ProductId) --Условие объединения
        --Если истина + доп. условие отработало (UPDATE)
        WHEN MATCHED AND T_Source.Summa IS NOT NULL THEN
                 UPDATE SET ProductName = T_Source.ProductName, Summa = T_Source.Summa
        WHEN NOT MATCHED THEN --Если НЕ истина (INSERT)
                 INSERT (ProductId, ProductName, Summa)
                 VALUES (T_Source.ProductId, T_Source.ProductName, T_Source.Summa)
        --Посмотрим, что мы сделали
        OUTPUT $action AS [Операция], Inserted.ProductId,
                   Inserted.ProductName AS ProductNameNEW, 
                   Inserted.Summa AS SummaNEW, 
                   Deleted.ProductName AS ProductNameOLD, 
                   Deleted.Summa AS SummaOLD; --Не забываем про точку с запятой
        --Итоговый результат
        SELECT * FROM dbo. TestTable
        SELECT * FROM dbo.TestTableDop

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

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

На этом у меня все, удачи!

Инструкция MERGE — SQL для Oracle

Без рубрики
sql oracle
·
24.07.2021
·

Выполняется операция 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).

Далее: Функции TO_YMINTERVAL и TO_DSINTERVAL

ОБЪЕДИНЕНИЕ

Указатель документации Firebird → Firebird 2.1 Language Ref. Обновить → операторы DML → MERGE

Доступно в: DSQL, PSQL

Добавлено в: 2.1

Описание: объединяет данные в таблицу или представление. Источником может быть таблица, представление или производная таблица (т. е. оператор SELECT в скобках или CTE). Каждая исходная запись будет использоваться для обновления одной или нескольких целевых записей, вставки новой записи в целевую таблицу или ни того, ни другого.
Предпринятое действие зависит от предоставленного условия и предложения WHEN. Условие обычно содержит сравнение полей исходного и целевого отношений.

Синтаксис:

 MERGE INTO {   имя_таблицы   |   имя представления   } [[AS]   псевдоним   ]
   ИСПОЛЬЗОВАНИЕ {   имя_таблицы   |   имя просмотра   | (   select_stmt   )} [[AS]   псевдоним   ]
   ПО   состояние  
   ПРИ СОВПАДЕНИИ, ТО ОБНОВЛЕНИЕ НАБОР   colname   =   значение   [   имя_колонки   =   значение   ...]
   ЕСЛИ НЕ СООТВЕТСТВУЕТ, ВСТАВЬТЕ [(   <столбцы>   )] ЗНАЧЕНИЯ (   <значения>   )
   <столбцы>   ::=   имя_столбца   [   имя_столбца   ...]
   <значения>   ::=   значение   [   значение   . ..]
  Примечание:  Разрешается предоставлять только одно из условий WHEN

Примеры:

 объединить в книги b
  с помощью покупок р
  на p.title = b.title и p.type = 'bk'
  при совпадении тогда
    набор обновлений b.desc = b.desc || '; ' || p.desc
  когда не совпадает тогда
    вставить (название, описание, купил) значения (p.title, p.desc, p.bought)

 объединить в клиентов c
  используя (выберите * из customers_delta, где id > 10) cd
  на (c.id = cd.id)
  при совпадении обновите имя набора = cd.name
  если не совпадают, вставьте (id, name) значения (cd.id, cd.name)

Примечание

WHEN NOT MATCHED следует интерпретировать с точки зрения источника (отношение в предложении USING). То есть: если исходная запись не имеет соответствия в целевой таблице, выполняется предложение INSERT. И наоборот, записи в целевой таблице без соответствующей исходной записи не вызывают никаких действий.

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

Если присутствует предложение WHEN MATCHED и несколько исходных записей соответствуют одной и той же записи в целевой таблице, предложение UPDATE выполняется для всех совпадающих исходных записей, при этом каждое обновление перезаписывает предыдущее. Это нестандартное поведение:
SQL-2003 указывает, что в таком случае должно быть возбуждено исключение.

Указатель документации Firebird → Firebird 2.1 Language Ref. Обновить → операторы DML → MERGE

SQL — вставка, удаление и обновление в ОДНОМ выражении: синхронизируйте свои таблицы с помощью MERGE | Майк Халс

Этот процесс может одновременно обрабатывать только одну таблицу; мы должны объединить (Изображение Роджера Брэдшоу на Pexels)

С помощью MERGE вы можете «синхронизировать» две таблицы, выполнив вставить , удалить и обновляют в ОДНОМ заявлении. Однако MERGE — это гораздо больше; он предлагает вам широкий спектр возможностей для сравнения и синхронизации таблиц. Вы даже можете отслеживать результат слияния. Мы рассмотрим их все в этой статье. Основная причина слияния:

Это атомарно; он либо выполняет все команды (например, обновить , вставить и удалить ), либо ничего. Если какая-либо из команд не работает, все откатывается.
Эффективно и быстро: SQL Server должен сравнивать записи один раз
Много гибкости
Впечатление вашего руководителя и коллег

Представьте, что у нас есть начинающая компания по производству пиццы. Мы следим за пиццей в таблице под названием Pizzamenu. Каждую неделю мы получаем новый набор данных, который содержит новейшее меню. Наша цель — обновить информацию о существующих пиццах, добавить новые и удалить старые. Давайте сначала создадим таблицы и вставим некоторые данные.

В этих запросах вы можете увидеть, что PizzaMenu и NewPizzaMenu практически идентичны. Единственное отличие состоит в том, что PizzaMenu содержит дополнительный столбец, который указывает, есть пицца в меню или нет (IsDeleted).

PizzaMenu и NewPizzaMenu

Как видите, новое меню больше не содержит Pizza Hawaii. Также исправлено название первой пиццы и цена на Тонно. Кроме того, добавляются две пиццы. Давайте узнаем, как это сделать.

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

Сопоставление исходной и целевой таблиц (Фото автора)

Сравнение происходит на основе нескольких столбцов и напоминает JOIN. Думайте о MERGE как о своего рода JOIN; он сравнивает записи двух таблиц. Вместо объединения LEFT, INNER и RIGHT мы можем думать о MERGE как об обнаружении и обработке записей, которые соответствуют «левым», «внутренним» и «правым». Проверьте это наш запрос:

При слиянии мы объединяем таблицу Target с таблицей Source. Таблица Target является источником правды. В первом блоке нашего запроса мы определяем, какими будут наши Источник (newPizzaMenu) и Цель (PizzaMenu). Как и в JOIN, мы определяем, какие значения столбцов должны совпадать, чтобы записи совпадали. В данном случае «PizzaId» и «Size».

— новые записи («правильное совпадение»)
Второй блок обрабатывает то, что происходит, когда в источнике появляются новые записи, которых еще нет в нашей цели. В нашем примере это относится к двум новым пиццам: Calzone L и XL. В этом блоке мы решили вставить новые записи в Target (PizzaMenu).

— совпадающие записи («внутреннее совпадение»)
Эта часть представляет собой центр диаграммы Венна; своего рода ВНУТРЕННЕЕ СОЕДИНЕНИЕ между нашими исходными и целевыми таблицами. В нашем случае совпадающими записями являются пицца Пепперони и Тонно. В этой части мы обновляем имя, цену и модификацию.

— устаревшие записи («левое совпадение»)
Обрабатывает записи, которые существуют в Цели, но отсутствуют в Источнике. Это представляет записи, которые присутствуют в нашем текущем меню, но не в новом меню, в нашем примере это представлено пиццей Гавайи. Мы решили не удалять эту запись (она закомментирована), вместо этого мы обновляем столбец IsDeleted в Target (наше текущее меню).

Когда мы выполняем запрос, мы видим:

Результат нашего слияния

Именно то, что мы хотели! Обратите внимание, что:

красный: из «правильного соответствия» → тонна пиццы удалена из меню
оранжевый: обновлено: исправлена цена и название первых двух пицц
зеленый: добавлены две нижние записи

Вы не нужно обновлять записи, которые появляются в части WHEN MATCHED; не стесняйтесь делать все, что вы хотите. Слияние просто определяет, соответствуют ли записи «левым», «правым» или «внутренним». Некоторые примеры:

обновлять только совпадающие записи и вставлять новые записи; ничего не удалять
Вставить запись в таблицу ошибок, если она появляется в левом или правом совпадении
Удалить все совпадающие записи из Цели и вставить только совпадающие справа (новые) записи

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

КОГДА СООТВЕТСТВУЕТ И цель.Имя == ‘Гавайи’
THEN Target.Name = ‘Запрещенный’
WHEN MATCHED AND target.stock != ‘Hawaii’
THEN Target.Name = Source.Name
Это обновляет запас, только если новый запас является положительным числом

Давайте теперь действительно запачкайте руки некоторыми бонусными использованиями (изображение от Cottonbro на Pexels)

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

С последними 5 записями мы собираемся вывести, какое действие мы выполнили и что именно мы изменили:

ВЫВОД нашего СЛИЯНИЯ

В первом столбце вы видите, какое действие мы выполнили, Затем, в 2-9 вы увидим информацию о вставленных нами записях.

imacrosoft.ru