Особенности команды UPSERT в различных СУБД

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

PostgreSQL

Конструкция: INSERT ... ON CONFLICT

• Синтаксис:

  INSERT INTO table_name (column1, column2, ...)
  VALUES (value1, value2, ...)
  ON CONFLICT (conflict_column)
  DO UPDATE SET column1 = value1, column2 = value2, ...;

• Описание:

  • Если возникает конфликт по уникальному ключу или первичному ключу, операция вставки заменяется на обновление.

  • Вы можете использовать условие для обновления, чтобы изменить только те строки, которые соответствуют определенным условиям.

  • Можно использовать DO NOTHING для пропуска вставки в случае конфликта.

• Пример:

  INSERT INTO employees (employee_id, name, salary)
  VALUES (1, 'John Doe', 75000)
  ON CONFLICT (employee_id)
  DO UPDATE SET name = EXCLUDED.name, salary = EXCLUDED.salary;

MySQL

Конструкция: INSERT ... ON DUPLICATE KEY UPDATE

• Синтаксис:

  INSERT INTO table_name (column1, column2, ...)
  VALUES (value1, value2, ...)
  ON DUPLICATE KEY UPDATE column1 = value1, column2 = value2, ...;

• Описание:

  • Если вставка приводит к нарушению уникального ключа или первичного ключа, выполняется обновление существующей записи.

  • Применяется к уникальным ключам и первичным ключам.

  • Можно использовать VALUES() для ссылки на новые значения в выражении обновления.

• Пример:

  INSERT INTO employees (employee_id, name, salary)
  VALUES (1, 'John Doe', 75000)
  ON DUPLICATE KEY UPDATE name = VALUES(name), salary = VALUES(salary);

SQLite

Конструкция: INSERT OR REPLACE, INSERT ON CONFLICT REPLACE, INSERT OR IGNORE, INSERT ON CONFLICT IGNORE

• Синтаксис:

  INSERT OR REPLACE INTO table_name (column1, column2, ...)
  VALUES (value1, value2, ...);

  INSERT OR IGNORE INTO table_name (column1, column2, ...)
  VALUES (value1, value2, ...);

• Описание:

  • INSERT OR REPLACE удаляет существующую запись с таким же значением уникального ключа и вставляет новую запись.

  • INSERT OR IGNORE вставляет запись только если уникальный ключ не нарушен.

  • SQLite также поддерживает ON CONFLICT для указания поведения при конфликте, включая REPLACE, IGNORE, и ABORT.

• Пример:

  INSERT OR REPLACE INTO employees (employee_id, name, salary)
  VALUES (1, 'John Doe', 75000);

SQL Server

Конструкция: MERGE

• Синтаксис:

  MERGE INTO target_table AS target
  USING source_table AS source
  ON target.key_column = source.key_column
  WHEN MATCHED THEN
      UPDATE SET target.column1 = source.column1, target.column2 = source.column2
  WHEN NOT MATCHED THEN
      INSERT (column1, column2)
      VALUES (source.column1, source.column2);

• Описание:

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

  • Подходит для сложных сценариев синхронизации данных.

  • Позволяет управлять конфликтами и изменениями данных.

• Пример:

  MERGE INTO employees AS e
  USING (VALUES (1, 'John Doe', 75000)) AS n (employee_id, name, salary)
  ON e.employee_id = n.employee_id
  WHEN MATCHED THEN
      UPDATE SET e.name = n.name, e.salary = n.salary
  WHEN NOT MATCHED THEN
      INSERT (employee_id, name, salary)
      VALUES (n.employee_id, n.name, n.salary);

Oracle

Конструкция: MERGE

• Синтаксис:

  MERGE INTO target_table t
  USING source_table s
  ON (t.key_column = s.key_column)
  WHEN MATCHED THEN
      UPDATE SET t.column1 = s.column1, t.column2 = s.column2
  WHEN NOT MATCHED THEN
      INSERT (column1, column2)
      VALUES (s.column1, s.column2);

• Описание:

  • Аналогично SQL Server, MERGE в Oracle используется для объединения вставки, обновления и удаления в одном запросе.

  • Позволяет управлять данными более гибко в зависимости от наличия совпадений.

• Пример:

  MERGE INTO employees e
  USING (SELECT 1 AS employee_id, 'John Doe' AS name, 75000 AS salary FROM dual) s
  ON (e.employee_id = s.employee_id)
  WHEN MATCHED THEN
      UPDATE SET e.name = s.name, e.salary = s.salary
  WHEN NOT MATCHED THEN
      INSERT (employee_id, name, salary)
      VALUES (s.employee_id, s.name, s.salary);

Заключение

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