Глиф Учебники

Вставить или обновить — ON CONFLICT и ON DUPLICATE KEY UPDATE

Справочник · SQL · 2 минуты чтения

Обычный INSERT строки с уже занятым ключом заканчивается ошибкой: UNIQUE constraint failed в SQLite, Duplicate entry в MySQL. Если в этом случае строку нужно обновить, пишут upsert — «вставить или обновить».

Пример — склад, где артикул sku уникален:

SQL
CREATE TABLE stock (
    sku  TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    qty  INTEGER NOT NULL
);

INSERT INTO stock VALUES ('A-1', 'Ручка', 10);

Вставить или обновить

Пришло ещё 5 ручек: если артикул есть — прибавить к остатку, если нет — добавить строку. Запись зависит от СУБД:

SQLite и PostgreSQLON CONFLICT

SQL
INSERT INTO stock (sku, name, qty) VALUES ('A-1', 'Ручка', 5)
ON CONFLICT (sku) DO UPDATE SET qty = stock.qty + excluded.qty;

SELECT * FROM stock;
skunameqty
A-1Ручка15
  • ON CONFLICT (sku) — что считать повтором: совпадение sku;
  • stock.qty — значение в существующей строке;
  • excluded.qty — значение, которое пытались вставить (5).

Заменить значения новыми — SET name = excluded.name, qty = excluded.qty. Работает в SQLite 3.24 и новее и в PostgreSQL 9.5 и новее.

Пропустить повтор молча, ничего не меняя:

SQL
INSERT INTO stock (sku, name, qty) VALUES
    ('A-1', 'Ручка', 1),
    ('B-2', 'Тетрадь', 30)
ON CONFLICT (sku) DO NOTHING;

Ручка осталась как была, тетрадь добавилась. В SQLite то же даёт короткое INSERT OR IGNORE INTO ….

Дальше: «Без уникального ключа не работает»

MySQL и MariaDBON DUPLICATE KEY UPDATE

SQL
INSERT INTO stock (sku, name, qty) VALUES ('A-1', 'Ручка', 5)
ON DUPLICATE KEY UPDATE qty = qty + VALUES(qty);

qty слева и справа от + — значение в существующей строке, VALUES(qty) — то, что пытались вставить. Повтором считается совпадение любого первичного ключа или уникального индекса таблицы — указать столбец нельзя.

В MySQL 8.0.20 и новее VALUES() здесь объявлена устаревшей, вместо неё — имя для новой строки:

SQL
INSERT INTO stock (sku, name, qty) VALUES ('A-1', 'Ручка', 5) AS new
ON DUPLICATE KEY UPDATE qty = stock.qty + new.qty;

Пропустить повтор — INSERT IGNORE INTO …. Учтите: IGNORE глушит и другие ошибки, например слишком длинное значение, превращая их в предупреждения.

Без уникального ключа не работает

Upsert опирается на PRIMARY KEY или UNIQUE. Если на столбце из ON CONFLICT их нет, SQLite ответит ошибкой ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint, PostgreSQL — похожей. MySQL ошибки не выдаст, а просто вставит дубликат. Поставьте уникальный индекс: CREATE UNIQUE INDEX idx_stock_sku ON stock (sku);.

REPLACE — не то же самое

INSERT OR REPLACE (SQLite) и REPLACE INTO (MySQL) похожи на upsert, но работают иначе: старая строка удаляется, а вместо неё вставляется новая.

При REPLACE теряются столбцы, которых нет в команде: они получат значения по умолчанию или NULL. Новая строка может получить другой id, а при внешних ключах с ON DELETE CASCADE удаление старой строки сотрёт и связанные строки других таблиц. Если нужно изменить строку, а не заменить её целиком, используйте ON CONFLICT … DO UPDATE или ON DUPLICATE KEY UPDATE.

Подводные камни