Home / SQL / Reference / Changing data

SQL INSERT … ON CONFLICT (upsert)

Insert a row, or update it if the key already exists.

Syntax

INSERT INTO t (…) VALUES (…)
ON CONFLICT (key) DO UPDATE SET col = excluded.col;

excluded is the row you tried to insert. DO NOTHING skips duplicates instead.

Example

Query (on the shop database used in the SQL course)

INSERT INTO products (product_id, name, category, price)
VALUES (3, 'USB-C hub', 'Electronics', 45.0)
ON CONFLICT (product_id) DO UPDATE SET price = excluded.price;
SELECT * FROM products WHERE product_id = 3;

Result

product_idnamecategoryprice
3USB-C hubElectronics45.0

1 row

Good to know. MySQL: ON DUPLICATE KEY UPDATE. SQL Server and Oracle: MERGE.

Related

Learn it step by step in the free lesson Changing Data, with exercises that check your queries.