What is an upsert, and which SQL dialects have a native way to express it?
An upsert is one statement that either adds a row or changes it, depending on whether a row with that key is already there.
the spellings:
PostgreSQL, SQLite INSERT ... ON CONFLICT ... DO UPDATE
MySQL INSERT ... ON DUPLICATE KEY UPDATE
SQL Server, Oracle MERGE INTO ...
So which dialects have a native way? Most of them, but the spelling differs, and that is worth knowing before you write a query that has to run on more than one engine.
CREATE TABLE inventory (
sku TEXT PRIMARY KEY,
name VARCHAR(120) NOT NULL,
qty INTEGER NOT NULL
);
-- The PostgreSQL and SQLite spelling.
INSERT INTO inventory (sku, name, qty) VALUES ('NB-001', 'Notebook', 5)
ON CONFLICT (sku) DO UPDATE SET qty = excluded.qty;
-- The MySQL spelling of the same thing.
INSERT INTO inventory (sku, name, qty) VALUES ('NB-001', 'Notebook', 5)
ON DUPLICATE KEY UPDATE qty = VALUES(qty);
And the standard way, which is the one that most engines now have, is the merge statement.
MERGE INTO inventory AS target
USING (VALUES ('NB-001', 'Notebook', 5)) AS source (sku, name, qty)
ON target.sku = source.sku
WHEN MATCHED THEN UPDATE SET qty = source.qty
WHEN NOT MATCHED THEN INSERT (sku, name, qty) VALUES (source.sku, source.name, source.qty);
There is one word in those spellings that is worth explaining. The word excluded is the name that the row you are trying to insert is given inside the update part, so excluded.qty means "the quantity that I asked to insert" and inventory.qty means "the quantity that is already stored".
The next two questions in this topic look at a full upsert and at the simpler alternative.