Что делает оператор merge sql

Применение оператора MERGE в PostgreSQL

Оператор MERGE – это мощный инструмент, который можно использовать для выполнения условных операций INSERT, UPDATE или DELETE строк в таблице. Он был введен в стандарте SQL:2003 и расширен в стандарте SQL:2008.

В этой статье мы рассмотрим, как использовать оператор MERGE в PostgreSQL для эффективного выполнения этих операций.

MERGE похож на оператор UPSERT в других диалектах SQL, но имеет ряд преимуществ, включая:

• Он более эффективен, поскольку выполняет только необходимые операции для каждой строки.
• Он атомарный, поскольку либо успешно выполняется, либо завершается неудачей целиком.
• Он более гибкий, поскольку позволяет указывать более сложные условия.

В PostgreSQL до версии 15 для условных операций INSERT , UPDATE и DELETE можно было использовать оператор INSERT INTO . ON CONFLICT [ conflict_target ] conflict_action . Однако, начиная с версии 15, появилась возможность использовать все преимущества оператора MERGE.

Синтаксис оператора MERGE выглядит следующим образом:

MERGE INTO target_table AS t USING source_table AS s ON WHEN MATCHED THEN WHEN NOT MATCHED THEN

Здесь target_table – это таблица, которую нужно обновить или в которую нужно вставить строки.

source_table – это таблица, содержащая данные, которые будут использоваться для обновления или вставки строк в target_table .

Условие – это логическое выражение, используемое для определения, нужно ли обновлять или вставлять строку из source_table .

update_statement – это SQL-запрос, используемый для обновления строк в target_table .

insert_statement – это SQL-запрос, используемый для вставки строк в target_table .

Вот пример оператора MERGE, который обновляет таблицу customers , используя таблицу leads . Обе таблицы имеют поле email, поэтому нам нужно добавлять только те строки из leads , email которых не существует в таблице customers :

MERGE INTO customers AS c USING leads AS l ON c.email = l.email WHEN NOT MATCHED THEN insert (name, email, created_at) VALUES (l.name, l.email, DEFAULT); 

Для каждой строки, для которой не нет соответствия, будет выполнен запрос на вставку.

Этот запрос эквивалентен следующему запросу UPSERT :

INSERT INTO customers (name, email) SELECT name, email FROM leads ON conflict (email) DO nothing; 

Но, если мы посмотрим на результаты, мы увидим преимущество оператора MERGE: он не увеличивает поле id в случае, если запись уже существует. Здесь можно выполнить тест SQL.

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

MERGE INTO customers AS c USING leads AS l ON c.email = l.email WHEN NOT MATCHED THEN INSERT (name, email, created_at) VALUES (l.name, l.email, DEFAULT) WHEN MATCHED THEN UPDATE SET name = l.name ; 

А в последнем сценарии мы добавим записи в таблицу customers с отсутствующими электронными адресами, обновим имя, если дата создания клиента меньше, чем соответствующая дата в leads , и удалим другие записи:

MERGE INTO customers AS c USING leads AS l ON c.email = l.email WHEN NOT MATCHED THEN INSERT (name, email, created_at) VALUES (l.email, l.name, DEFAULT) WHEN MATCHED AND c.created_at < l.created_at THEN UPDATE SET name = l.name WHEN MATCHED THEN DELETE ;

Таким образом мы познакомились с командой MERGE , которая позволяет выполнять условные операции INSERT , UPDATE или DELETE на строках таблицы. Этот инструмент более эффективен, атомарен и гибок, чем другие SQL-диалекты.

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

Если Вам понравилась статья, Вы можете поддержать автора.

Что делает оператор merge sql

MERGE — добавить, изменить или удалить строки таблицы по условию

Синтаксис

[ WITH запрос_WITH [, . ] ] MERGE INTO [ ONLY ] имя_целевой_таблицы [ * ] [ [ AS ] целевой_псевдоним ] USING источник_данных ON условие_соединения предложение_when [. ] здесь источник_данных: < [ ONLY ] имя_исходной_таблицы [ * ] | ( исходный_запрос ) > [ [ AS ] исходный_псевдоним ] и предложение_when: < WHEN MATCHED [ AND условие ] THEN < изменение_при_объединении | удаление_при_объединении | DO NOTHING > | WHEN NOT MATCHED [ AND условие ] THEN < добавление_при_объединении | DO NOTHING > > и добавление_при_объединении: INSERT [( имя_столбца [, . ] )] [ OVERRIDING < SYSTEM | USER >VALUE ] < VALUES ( < выражение | DEFAULT > [, . ] ) | DEFAULT VALUES > и изменение_при_объединении: UPDATE SET < имя_столбца = < выражение | DEFAULT > | ( имя_столбца [, . ] ) = ( < выражение | DEFAULT > [, . ] ) > [, . ] и удаление_при_объединении: DELETE

Описание

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

Сначала команда MERGE выполняет соединение источника_данных с таблицей имя_целевой_таблицы , формируя ноль или более строк-кандидатов на изменение. Для каждой строки-кандидата устанавливается неизменяемый позже статус MATCHED (совпадает) или NOT MATCHED (не совпадает), после чего вычисляются условия WHEN в заданном порядке. Для каждой отдельной строки будет выполняться действие первого же предложения, условие которого выдаст true. При этом для каждой строки-кандидата может быть выполнено действие не более чем одного предложения WHEN .

Действия операции MERGE имеют тот же эффект, что и обычные одноимённые команды UPDATE , INSERT или DELETE . Синтаксис этих команд в MERGE отличается, в частности, отсутствием предложения WHERE и имени таблицы. Действия этих команд выполняются с таблицей имя_целевой_таблицы , хотя посредством триггеров могут быть изменены и другие таблицы.

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

Для MERGE не предусмотрено отдельное право. Когда в этой команде указывается действие UPDATE , у вас должно быть право UPDATE для столбцов таблицы имя_целевой_таблицы , на которые ссылается предложение SET . Когда указывается действие INSERT или DELETE , у вас должно быть соответствующее право для таблицы имя_целевой_таблицы . Права проверяются один раз в начале выполнения оператора, вне зависимости от того, будут ли выполняться конкретные предложения WHEN . Кроме того, необходимо иметь право SELECT для источника_данных и столбцов таблицы имя_целевой_таблицы , которые фигурируют в условии .

Оператор MERGE не поддерживается для отношений, являющихся материализованными представлениями, сторонними таблицами, или если для них заданы какие-либо правила.

Параметры

имя_целевой_таблицы

Имя (возможно, дополненное схемой) целевой таблицы, принимающей результат объединения. Если перед именем таблицы добавлено ONLY , соответствующие строки изменяются или удаляются только в указанной таблице. Без ONLY соответствующие строки также изменяются или удаляются во всех таблицах, унаследованных от указанной таблицы. При желании, после имени таблицы можно указать * , чтобы явно обозначить, что операция затрагивает все дочерние таблицы. Ключевое слово ONLY и параметр * не влияют на действия INSERT , добавляющие строки только в указанную таблицу. целевой_псевдоним

Альтернативное имя целевой таблицы. Когда это имя задаётся, настоящее имя таблицы полностью скрывается. Например, в запросе MERGE INTO foo AS f остальные компоненты оператора MERGE должны обращаться к целевой таблице по имени f , а не foo . имя_исходной_таблицы

Имя (возможно, дополненное схемой) исходной таблицы, представления или переходной таблицы. Если перед именем таблицы добавлено ONLY , соответствующие строки берутся только из указанной таблицы. Без ONLY строки также берутся из всех таблиц, унаследованных от указанной. При желании, после имени таблицы можно указать * , чтобы явно обозначить, что операция затрагивает все дочерние таблицы. исходный_запрос

Запрос (оператор SELECT или оператор VALUES ), предоставляющий строки для объединения в таблице имя_целевой_таблицы . За информацией о синтаксисе обратитесь к описанию SELECT и VALUES . исходный_псевдоним

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

Задаваемое условие_соединения представляет собой выражение, выдающее значение типа boolean (как в предложении WHERE ), которое определяет, какие строки в источнике_данных соответствуют строкам в таблице имя_целевой_таблицы .

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

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

предложение_when

В команде MERGE должно быть минимум одно предложение WHEN .

Если в предложении WHEN указано WHEN MATCHED и строка-кандидат на изменение соответствует строке таблицы имя_целевой_таблицы , предложение WHEN выполняется, когда условие отсутствует или выдаёт true .

И наоборот, если в предложении WHEN указано WHEN NOT MATCHED и строка-кандидат на изменение не соответствует строке таблицы имя_целевой_таблицы , предложение WHEN выполняется, когда условие отсутствует или выдаёт true . условие

Выражение, выдающее значение типа boolean . Если это выражение для предложения WHEN выдаёт true , для данной строки выполняется действие этого предложения.

Условие в предложении WHEN MATCHED может ссылаться на столбцы как исходного, так и целевого отношения. Условие в предложении WHEN NOT MATCHED может ссылаться только на столбцы исходного отношения, поскольку соответствующей целевой строки нет по определению. В целевой таблице доступны только системные атрибуты. добавление_при_объединении

Указание действия INSERT , добавляющего одну строку в целевую таблицу. Имена целевых столбцов могут перечисляться в любом порядке. Если список имён столбцов не задан вовсе, по умолчанию используются все столбцы таблицы в порядке объявления.

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

Если таблица имя_целевой_таблицы является секционированной, каждая строка направляется в соответствующую секцию и добавляется в неё. Если таблица имя_целевой_таблицы является секцией и какая-либо входная строка нарушит ограничение секции, произойдёт ошибка.

Имена столбцов нельзя указывать более одного раза. Действия INSERT не могут содержать вложенные запросы SELECT .

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

Указание действия UPDATE , изменяющего текущую строку таблицы имя_целевой_таблицы . Имена столбцов нельзя указывать более одного раза.

Задавать имя таблицы и предложение WHERE здесь нельзя. удаление_при_объединении

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

Имя столбца таблицы имя_целевой_таблицы . При необходимости имя столбца можно дополнить именем поля или индексом массива. (При добавлении данных лишь в некоторые поля составного типа другие поля будут содержать NULL.) Имя таблицы в указание целевого столбца добавлять не нужно. OVERRIDING SYSTEM VALUE

Без этого предложения не допускается задание явного значения (отличного от DEFAULT ) для столбца идентификации, определённого с характеристикой GENERATED ALWAYS . Данное предложение перекрывает это ограничение. OVERRIDING USER VALUE

Если указывается это предложение, то значения, заданные для столбцов идентификации, которые определены с характеристикой GENERATED BY DEFAULT , игнорируются и вместо них применяются значения, выдаваемые последовательностями по умолчанию. DEFAULT VALUES

Все столбцы получают значения по умолчанию. (Предложение OVERRIDING в этой форме не допускается.) выражение

Выражение, результат которого присваивается столбцу. В выражениях предложений WHEN MATCHED могут использоваться значения из исходной строки целевой таблицы и значения из строки источника_данных . В выражениях предложений WHEN NOT MATCHED могут использоваться значения только из источника_данных . DEFAULT

Присвоить столбцу значение по умолчанию (или NULL , если выражение по умолчанию для столбца не определено). запрос_WITH

Предложение WITH позволяет задать один или несколько подзапросов, на которые затем можно ссылаться по имени в запросе MERGE . За подробностями обратитесь к Разделу 7.8 и SELECT .

Выводимая информация

При успешном выполнении команда MERGE возвращает метку команды в виде

MERGE общее_число 

Здесь общее_число — суммарное количество изменённых строк (добавленных, изменённых или удалённых). Если общее_число равно 0, ни одна строка не была изменена.

Замечания

В ходе выполнения MERGE производятся следующие действия.

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

Выполняется соединение исходной таблицы с целевой. Полученный в результате запрос оптимизируется как обычно и выдаёт набор строк-кандидатов на изменение. Для каждой строки-кандидата на изменение:

Для каждой строки определяется состояние: MATCHED (совпадает) или NOT MATCHED (не совпадает).

Проверяется каждое условие WHEN в заданном порядке, пока какое-либо не выдаст значение true.

Если условие выдаёт true, происходит следующее:

Вызываются все триггеры BEFORE ROW , соответствующие типу события выполняемого действия.

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

То есть триггеры уровня оператора для некоторого события (скажем, INSERT ) будут вызываться всегда, когда указывается действие такого типа. Триггеры уровня строк, напротив, вызываются только для определённого действия, которое выполняется . Таким образом, при выполнении MERGE могут вызываться триггеры уровня оператора как для UPDATE , так и для INSERT , даже если на уровне строк вызывались только триггеры UPDATE .

Следует позаботиться о том, чтобы для каждой целевой строки в результате соединения создавалось не более одной строки-кандидата на изменение. Другими словами, целевая строка не должна соединяться с более чем одной строкой источника данных. Если это не так, только одна из строк-кандидатов будет применяться для изменения целевой строки; последующие попытки изменить эту строку вызовут ошибку. Ошибка также может произойти, когда триггеры строк вносят изменения в целевую таблицу, а команда MERGE впоследствии воздействует на уже изменённые строки. Если повторится действие INSERT , это вызовет нарушение уникальности, а повторение UPDATE или DELETE вызовет ошибку « Нарушение количества » ; последнее требуется стандартом SQL . Такое поведение отличается от поведения соединений в UPDATE и DELETE , традиционного для PostgreSQL , когда вторая и последующие попытки изменить одну и ту же строку просто игнорируются.

Если в предложении WHEN отсутствует дополнительное условие AND , оно становится последним достижимым предложением этого рода ( MATCHED или NOT MATCHED ). Если в команде встретится последующее предложение WHEN такого рода, оно гарантированно будет недостижимым, и это вызовет ошибку. В случае отсутствия последнего достижимого предложения любого рода возможна ситуация, когда для строки-кандидата на изменение не будет предпринято никаких действий.

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

В операторе MERGE не допускается предложение RETURNING . Действия INSERT , UPDATE и DELETE также не могут содержать предложения RETURNING или WITH .

Когда MERGE выполняется одновременно с другими командами, изменяющими целевую таблицу, применяются обычные правила изоляции транзакций; поведение на каждом уровне изоляции описано в Разделе 13.2. В качестве альтернативы можно рассмотреть использование оператора INSERT . ON CONFLICT , который предусматривает возможность выполнения команды UPDATE , если параллельно выполняется команда INSERT . Эти два типа операторов имеют ряд различий и особых ограничений, они не являются взаимозаменяемыми.

Примеры

Корректировка клиентских счетов ( customer_accounts ) с учётом новых транзакций ( recent_transactions ).

MERGE INTO customer_account ca USING recent_transactions t ON t.customer_id = ca.customer_id WHEN MATCHED THEN UPDATE SET balance = balance + transaction_value WHEN NOT MATCHED THEN INSERT (customer_id, balance) VALUES (t.customer_id, t.transaction_value);

Заметьте, что это полностью равнозначно следующему оператору, потому что статус MATCHED не меняется во время выполнения.

MERGE INTO customer_account ca USING (SELECT customer_id, transaction_value FROM recent_transactions) AS t ON t.customer_id = ca.customer_id WHEN MATCHED THEN UPDATE SET balance = balance + transaction_value WHEN NOT MATCHED THEN INSERT (customer_id, balance) VALUES (t.customer_id, t.transaction_value);

Обработка изменений количества товара: новая позиция добавляется вместе с количеством; если данная позиция уже существует, её количество корректируется; позиции с нулевым количеством удаляются.

MERGE INTO wines w USING wine_stock_changes s ON s.winename = w.winename WHEN NOT MATCHED AND s.stock_delta > 0 THEN INSERT VALUES(s.winename, s.stock_delta) WHEN MATCHED AND w.stock + s.stock_delta > 0 THEN UPDATE SET stock = w.stock + s.stock_delta WHEN MATCHED THEN DELETE;

Таблица wine_stock_changes может быть, например, временной таблицей, недавно загруженной в базу данных.

Совместимость

Эта команда соответствует стандарту SQL .

Предложение WITH и действие DO NOTHING являются расширениями стандарта SQL .

Пред.	Наверх	След.
LOCK	Начало	MOVE

Oracle PL/SQL оператор MERGE

Oracle PL/SQL оператор MERGE позволяет выбрать строки из одной таблицы для обновления или вставки в другую таблицу. Решение о том, обновлять или вставлять данные в целевую таблицу, основывается на условии в предложении ON . Оператор MERGE - это удобный способ совместить как минимум две операции. Это позволяет избежать использования нескольких DML операторов INSERT и UPDATE.
У вас должны быть объектные привилегии INSERT и UPDATE для целевой таблицы и привилегии SELECT для исходной таблицы.

Синтаксис

Синтаксис Oracle PL/SQL оператора MERGE:

MERGE [hint] INTO [schema .] table_A [t_alias] USING [schema .]
< table | view | subquery >[t_alias] ON ( condition )
WHEN MATCHED THEN merge_update_clause
WHEN NOT MATCHED THEN merge_insert_clause;

Параметры или аргумент

schema схема базы данных. INTO используйте оператор INTO, чтобы указать целевую таблицу, которую вы обновляете или вставляете данные. table_A целевая таблица. t_alias алиас (псевдоним). USING < table | view | subquery >указывает источник данных, из которого нужно обновить или вставить данные в table_A . Это может быть таблица, представление или результат подзапроса. ON ( condition ) указывает условие, при котором операция MERGE либо обновляет, либо вставляет данные. Для каждой строки table_A , для которой выполняется условие поиска, Oracle обновляет строку, используя соответствующие данные из исходной таблицы. Если условие не выполняется ни для одной строки, Oracle вставляет в table_A данные на основе соответствующей строки исходной таблицы. WHEN MATCHED | NOT MATCHED Используйте эти предложения, чтобы указать Oracle, как реагировать на результаты условия соединения в предложении ON. Вы можете указать эти два предложения в любом порядке. merge_update_clause определяет новые значения столбцов table_A . Oracle выполняет это обновление, если условие предложения ON истинно. Если предложение обновления выполняется, то активируются все триггеры обновления, определенные в table_A .
Пример merge_update_clause: UPDATE SET column = < expr | DEFAULT >[, column = < expr | DEFAULT >]

Ограничения на обновление View

• Вы не можете указать DEFAULT при обновлении View.
• Вы не можете обновить столбец, на который есть ссылка в условии ON.

Ограничение на слияние с View

• Вы не можете указать DEFAULT при обновлении View.

Примеры Oracle PL/SQL MERGE

В следующем примере создается таблица bonuses в схеме oe с полем bonus равным 100, по умолчанию.

Merge (SQL)

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

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

Пример

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 11g
• Документация SQL Server 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

• Обратная связь: Техподдержка, Реклама на сайте

• �� Путешествия

Экспорт словарей на сайты, сделанные на PHP,
WordPress, MODx.

• Пометить текст и поделитьсяИскать в этом же словареИскать синонимы
• Искать во всех словарях
• Искать в переводах
• Искать в ИнтернетеИскать в этой же категории