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_id | name | category | price |
|---|---|---|---|
| 3 | USB-C hub | Electronics | 45.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.