SQL - MERGE & UPSERT
Overview
Upsert means insert a row if it doesn’t exist, otherwise update. Syntax varies across engines.
-- MySQL upsert
INSERT INTO departments (id, name)
VALUES (1, 'Engineering')
ON DUPLICATE KEY UPDATE name = VALUES(name);
-- SQL Server MERGE
MERGE dbo.departments AS t
USING (VALUES (1, N'Engineering')) AS s(id, name)
ON t.id = s.id
WHEN MATCHED THEN UPDATE SET t.name = s.name
WHEN NOT MATCHED THEN INSERT (id, name) VALUES (s.id, s.name);
-- SQLite upsert (3.24+)
INSERT INTO departments (id, name)
VALUES (1, 'Engineering')
ON CONFLICT(id) DO UPDATE SET name=excluded.name;