MERGE / REPLACE / UPSERT

В SQL и некоторых СУБД существуют операции MERGE, REPLACE и UPSERT, которые помогают упростить обработку данных при выполнении операций вставки и обновления. Каждая из них имеет свои особенности и применяется в различных сценариях.

1. MERGE

Описание: Команда MERGE объединяет операции вставки, обновления и удаления в одном запросе, позволяя эффективно управлять данными на основе условий. Это особенно полезно для синхронизации данных между таблицами.

Синтаксис (SQL Server):

MERGE target_table AS target
USING source_table AS source
ON target.id = source.id
WHEN MATCHED THEN
    UPDATE SET target.value = source.value
WHEN NOT MATCHED THEN
    INSERT (id, value) VALUES (source.id, source.value)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

Пример:

MERGE INTO employees AS target
USING (SELECT id, name FROM new_employees) AS source
ON target.id = source.id
WHEN MATCHED THEN
    UPDATE SET target.name = source.name
WHEN NOT MATCHED THEN
    INSERT (id, name) VALUES (source.id, source.name);

В этом примере:

• Если запись с таким же id уже существует в таблице employees, то она обновляется.

• Если запись с таким id не найдена, то создается новая запись.

Поддержка: MERGE поддерживается в SQL Server, Oracle, PostgreSQL (в версии 15 и новее) и других СУБД.

2. REPLACE

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

Синтаксис (MySQL):

REPLACE INTO table_name (id, value) VALUES (1, 'NewValue');

Пример:

REPLACE INTO products (product_id, product_name) VALUES (123, 'New Product');

В этом примере:

• Если запись с product_id = 123 существует, она будет заменена новой записью.

• Если такой записи нет, будет добавлена новая запись.

Поддержка: REPLACE поддерживается в MySQL и некоторых других СУБД.

3. UPSERT

Описание: Операция UPSERT (сокращение от "update or insert") позволяет вставить новую запись или обновить существующую запись в зависимости от того, существует ли уже запись с таким же уникальным ключом. Эта операция часто используется для упрощения обработки конфликтов уникальных ключей.

Синтаксис (PostgreSQL):

INSERT INTO table_name (id, value) VALUES (1, 'NewValue')
ON CONFLICT (id) 
DO UPDATE SET value = EXCLUDED.value;

Пример:

INSERT INTO users (user_id, username) VALUES (1, 'JohnDoe')
ON CONFLICT (user_id)
DO UPDATE SET username = EXCLUDED.username;

В этом примере:

• Если запись с user_id = 1 существует, она будет обновлена.

• Если записи с таким user_id нет, будет создана новая запись.

Поддержка: UPSERT поддерживается в PostgreSQL (с версии 9.5), SQLite, Oracle (через MERGE), SQL Server (через MERGE), и других СУБД.

Выводы

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

• REPLACE: Простой способ обновления или вставки данных, где существующие записи заменяются новыми. Поддерживается в MySQL.

• UPSERT: Обеспечивает вставку новых записей или обновление существующих, основываясь на уникальных ключах. Поддерживается в PostgreSQL, SQLite и других СУБД.

Выбор между этими операциями зависит от конкретных требований задачи и используемой СУБД.