Что такое merge oracle

Обновлено: 07.07.2024

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

Эта задача подразумевает выполнение двух разных операторов (INSERT и UPDATE) на одной таблице (Ships) в зависимости от наличия/отсутствия связанных записей в другой таблице (Outcomes).

Для решения подобных задач стандарт предоставляет оператор MERGE . Рассмотрим его использование на примере решения данной задачи в SQL Server.

Для начала напишем запрос, который вернет нам головные корабли из таблицы Outcomes, т.е. корабли, у которых имя класса совпадает с именем корабля:

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

Исходные данные готовы. Теперь мы можем перейти к написанию оператора MERGE.

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

Поскольку удаления в нашем запросе не было, то соответствующие столбцы имеют значения NULL. Столбец $action содержит название выполненной операции. В нашем случае была выполнена только вставка, поскольку корабль Tennessee содержится в таблице Ships с известным годом спуска на воду:

Инструкция MERGE может иметь не больше двух предложений WHEN MATCHED .

Если указаны два предложения, то первое предложение должно сопровождаться дополнительным условием (что имеет место в нашем случае - AND target.launched IS NULL). Для любой строки второе предложение WHEN MATCHED применяется только в том случае, если не применяется первое.

Если имеются два предложения WHEN MATCHED, одно должно указывать действие UPDATE, а другое — DELETE. Т.е. если мы добавим в оператор предложение

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

Предложение WHEN NOT MATCHED [BY TARGET] THEN INSERT используется для вставки строк из источника, не совпадающих со строками в изменяемой таблице согласно условию связи. В нашем примере такой строкой является строка, относящаяся к кораблю Bismarck. Инструкция MERGE может иметь только одно предложение WHEN NOT MATCHED.

Наконец, оператор MERGE может включать предложение WHEN NOT MATCHED BY SOURCE THEN.

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

При помощи этого предложения можно удалять или обновлять строки. Инструкция MERGE может иметь не более двух предложений WHEN NOT MATCHED BY SOURCE. Если указаны два предложения, то первое предложение должно иметь дополнительное условие (как в нашем примере). Для любой выбранной строки второе предложение WHEN NOT MATCHED BY SOURCE применяется только в тех случаях, если не применяется первое. Кроме того, если имеется два предложения WHEN NOT MATCHED BY SOURCE, то одно должно выполнять UPDATE, а другое — DELETE.

Команда 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 необходимо:

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

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

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

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

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

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

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

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

А для того, чтобы в одном запросе выбрать вставляемые, модифицируемые, а также удаляемые строки, нужно воспользоваться полным внешним соединением, 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 должны произойти следующие изменения:

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

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

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

Merge относится к DML (Data Manipulation Language) языка SQL. Оператор появился с версии SQL Server 2008 и используется для того, чтобы привести 2 набора данных в соответствие — добавить строки, если их нет, обновить, если строки найдены, и удалить (опционально).

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

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

Разберём каждую часть

Target table — целевая таблица, именно в ней данные будут добавлены, изменены или удалены в результате выполнения оператора.

Source table — исходная таблица или таблица-источник данных, с которой будет сравниваться целевая таблица, это может быть любой набор данных. Предложение USING напоминает предложение FROM из оператора SELECT, тут можно указать таблицу, подзапрос, табличное выражение или функцию, возвращающую таблицу.

Далее ON — условие соединения двух таблиц (а точнее, наборов данных), такое же как ON в JOIN.

Теперь посмотрим на соотношение

Следующие 3 части оператора

When Matched — описывает действие, которое срабатывает для строк, которые нашлись и в Source, и в Target по условию, которое описано в ON. В этой части чаще всего встречается оператор UPDATE, хотя возможно использование оператора DELETE.

When Not Matched [By Target] — описывает действие для строк, которые есть в таблице Source, но отсутствуют в таблице Target; далее используется оператор INSERT, и указанные строки добавляются в таблицу Target.

When Not Matched By Source — описывает действие для строк, которые отсутствуют в таблице Source, но найдены в таблице Target, чаще всего встречается оператор DELETE, чтобы удалить строки и привести 2 набора в соответствие, но возможно использование оператора Update.

Если переложить на оператор, то получится:

Например, возьмём загрузку данных о наличие книг:

Строки 1 и 2 — произойдёт Update, строки 3 и 4 — Insert, а строка 5 — из таблицы Target будет удалена Delete.

И последняя часть оператора в примере

OUTPUT — отличается тем, что есть специальная функция $action , которая возвращает оператор, который был применён для конкретной строки. Служебные таблицы deleted показывают данные, которые были в Target до выполнения Merge. Inserted — данные, которые возникли после применения Merge.

Ниже пример кода с Merge:

Что можно почитать по Merge: 1. Ben-Gan I., Sarka D., Talmage R. — Training Kit (Exam 70-461) Querying Microsoft SQL Server 2012-2012, Глава 11. 2. Документация. 3. Статья.

Инструкция MERGE cоветы и хитрости программирования

Содержание

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

Единственный сложный аспект инструкции MERGE - это принять саму идею обработки инструкций INSERT я UPDATE по принципу или-или.

Предположим, что у нас есть две таблицы. В таблице ЕМР хранятся сведения обо всех сотрудниках компании, которые успешно прошли 90-дневный испытательный срок при поступлении на работу. Сотрудники, входящие в таблицу ЕМР, также могут иметь несколько состояний - «работает», «не работает» и «уволен». Прием в компанию каждого нового служащего записывается в таблицу NEWJHIRE. Через 90 дней записи переносятся в таблицу ЕМР, где описываются все обычные сотрудники. Однако, поскольку наша компания каждое лето принимает практикантов из колледжа, очень вероятно, что у некоторых из наших новых сотрудников с прошлого года останутся записи в таблице ЕМР с состоянием «не работает». Эту бизнес-проблему можно описать при помощи следующего псевдокода.

Для каждой записи в таблице NEW_HIRE

Найти соответствующую запись в таблице ЕМР Если запись существует в таблице ЕМР

Обновить существующие данные в таблице ЕМР Иначе

Вставить запись в таблицу ЕМР Конец оператора Если Конец оператора Для

Мы можем написать довольно длинную хранимую процедуру, которая будет проверять все записи в таблице NEWJHIRE, а затем по условию вставлять (INSERT) в таблицу ЕМР записи о совершенно новых сотрудниках или обновлять (UPDATE) записи возвращающихся практикантов. Но следующая инструкция MERGE стандарта ANSI значительно упрощает этот процесс.

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

Платформа DB2 поддерживает для инструкции MERGE синтаксис стандарта ANSI.

MySQL

Oracle

Платформа Oracle поддерживает инструкцию MERGE с очень небольшими вариациями, которые почти все явно видны при сравнении схемы синтаксиса Oracle со схемой синтаксиса ANSI.

[схема.]ссылка_на_таблицу [псевдоним] ON (условие_поиска) WHEN MATCHED THEN

UPDATE SET столбец= [, …] WHEN NOT MATCHED THEN

INSERT (столбец [, …]) VALUES (выражение [, …]

К различиям между стандартом ANSI и его реализацией в Oracle относятся следующие.

Платформа Oracle не позволяет использовать ключевое слово AS для присвоения псевдонима целевой таблице или таблице-источнику.
Orucle требует заключать условие рожка в скобки.
В предложении WHEN NOT MATCHED требуется указывать список вставляемых столбцов, хотя в стандарте ANSI это не является обязательным.

За дополнительной информацией обращайтесь к разделам «Общие правила» и «Советы и хитрости программирования».

PostgreSQL

SQL Server

Дополнительная информация по теме

Некоторые хитрости при использования инструкции REVOKE в базах данных на различных платформах

Советы по использованию инструкции UPDATE в базах данных на самых разнообразных платформах

Некоторые особенности использования инструкции INSERT в базах данных на различных платформах

Некоторые советы и методы использования инструкции FETCH в базах данных на различных платформах

Читайте также: