Merge (SQL)

Реляційні системи керування базами даних використовують оператори SQL MERGE (також звані upsert) для вставляння нових записів або оновлення наявних залежно від збігів за умовою. Цей оператор було офіційно впроваджено у стандарті SQL:2003 та розширено у стандарті SQL:2008.

Застосування

MERGE INTO tablename USING table_reference
ON (condition)
  WHEN MATCHED THEN
    UPDATE
    SET column1 = value1 [, column2 = value2 ...]
  WHEN NOT MATCHED THEN
    INSERT (column1 [, column2 ...])
    VALUES (value1 [, value2 ...]);

До цілі (таблиця INTO) та джерела (таблиця, розріз або підзапит USING) застосовується праве зовнішнє з'єднання, в якому ціль є лівою таблицею, а джерело — правою. Чотири можливі комбінації відповідають таким правилам:

• Якщо поля ON у джерелі збігаються з полями ON у цілі, то UPDATE
• Якщо поля ON у джерелі не збігаються з полями ON у цілі, то INSERT
• Якщо полів ON не існує у джерелі, але є в цілі, то нічого не відбувається.
• Якщо полів ON не існує ані в джерелі ані в цілі, то нічого не відбувається.

Якщо кілька рядків у джерелі відповідають одному рядкові в цілі, то, згідно стандарту SQL:2003, виникає помилка. Оновлювати рядки в цілі за допомогою оператора MERGE декілька разів не можна.

Реалізації

Системи керування базами даних Oracle Database, DB2, Teradata, EXASOL, Firebird, CUBRID, HSQLDB, MS SQL, Vectorwise, Apache Derby та BigQuery підтримують стандартний синтаксис. Деякі також додають нестандартні розширення SQL.

Синоніми

В деяких реалізаціях баз даних для операторів або їх комбінацій, що вставляють запис до таблиці бази даних, якщо його не існує, або ж оновлюють наявний запис, було обрано термін «Upsert» (словозлиття update та insert). Його також застосовують як скорочений варіант запису MERGE у псевдокоді.

Він використовується в Microsoft SQL Azure.

Інші нестандартні реалізації

Деякі інші системи керування базами даних підтримують таку ж або дуже схожу поведінку за допомогою власних, нестандартних розширень SQL.

MySQL, наприклад, підтримує використання синтаксису INSERT ... ON DUPLICATE KEY UPDATE, який можна використовувати для досягнення схожого ефекту, з тим обмеженням, що з'єднання між ціллю та джерелом має відбуватися винятково за примусів PRIMARY KEY чи UNIQUE, чого не вимагає стандарт ANSI/ISO. Вона також підтримує синтаксис REPLACE INTO, який спочатку намагається вставити, а у разі невдачі видаляє рядок, якщо той існує, та вставляє новий. Існує також вираз IGNORE для оператора INSERT, що каже серверу ігнорувати помилки «дублікат ключа» та продовжувати (наявні рядки не вставлятимуться й не оновлюватимуться, але всі нові вставлятимуться).

Подібним чином працює INSERT OR REPLACE INTO в SQLite. Він також підтримує REPLACE INTO як псевдонім для сумісності з MySQL.

Firebird підтримує MERGE INTO, хоча й не може видавати помилку, коли джерело містить кілька рядків. Крім того, існує однорядковий варіант UPDATE OR INSERT INTO tablename (columns) VALUES (values) [MATCHING (columns)], але він не дозволяє виконувати різні дії при вставці чи оновленні (наприклад, встановлення нового значення послідовності лише для нових записів, а не наявних).

IBM DB2 розширює синтаксис кількома виразами WHEN MATCHED і WHEN NOT MATCHED, розрізняючи їх вартами ... AND some-condition.

Microsoft SQL Server розширюється підтримкою варт, а також підтримкою лівого зовнішнього з'єднання за допомогою виразів WHEN NOT MATCHED BY SOURCE.

PostgreSQL підтримує злиття за допомогою INSERT INTO ... ON CONFLICT [ conflict_target ] conflict_action.

CUBRID підтримує оператор MERGE INTO, використання синтаксису INSERT ... ON DUPLICATE KEY UPDATE, а також REPLACE INTO для сумісності з MySQL.

Apache Phoenix підтримує синтаксис UPSERT VALUES і UPSERT SELECT.

Див. також

• Join (SQL)
• join (Unix)

Література

• Хсу, Лео; Обе, Регіна (18 травня 2008). Cross Compare of SQL Server, MySQL, and PostgreSQL. Postgres OnLine Journal. Процитовано 8 жовтня 2010.
• Чодоров, Крістіна; Діролф, Майк (вересень 2010). MongoDB: The Definitive Guide. O'Reilly Media. ISBN 978-1-449-38156-1.

Посилання

• Документація Oracle 11g Release 2 з MERGE
• Документація Firebird 2.1 з MERGE
• Оператор MERGE у DB2 v9
• Документація Microsoft SQL Server
• Оператори зміни даних HSQLdb 2.0
• Сторінка синтаксису SQL у H2 (1.2)