ON CONFLICT (upsert)
ON CONFLICT lets an INSERT update the existing row or do nothing when a unique key already exists.
In this page:
Syntax
INSERT INTO table_name (key_column, column2)
VALUES (value1, value2)
ON CONFLICT (key_column)
DO UPDATE SET column2 = EXCLUDED.column2;ON CONFLICT (upsert)
INSERT ... ON CONFLICT (column) DO UPDATE SET ... inserts a row or updates the existing one, which is an upsert. DO NOTHING skips the insert quietly.
Inside DO UPDATE the special EXCLUDED table refers to the values you tried to insert. The conflict target must have a unique index or constraint. This syntax also works on modern SQLite.
Note:
EXCLUDED.column holds the value from the failed insert.
Example: ON CONFLICT (upsert)
CREATE TABLE stock (sku TEXT PRIMARY KEY, qty INTEGER);
INSERT INTO stock VALUES ('PEN', 10);
INSERT INTO stock (sku, qty) VALUES ('PEN', 5) ON CONFLICT (sku) DO UPDATE SET qty = qty + excluded.qty;
INSERT INTO stock (sku, qty) VALUES ('PEN', 99) ON CONFLICT (sku) DO NOTHING;
INSERT INTO stock (sku, qty) VALUES ('LAMP', 3) ON CONFLICT (sku) DO NOTHING;
SELECT * FROM stock ORDER BY sku;
-- Output:
-- sku | qty
-- LAMP | 3
-- PEN | 15Login to try C/C++/Java/PHP code in the editor
Related Topics
Common Mistakes
- Conflict target without a unique constraint
- Forgetting the conflict column list
- Not using EXCLUDED to reuse the new values
Chapter Summary
- ON CONFLICT handles duplicate keys
- DO NOTHING or DO UPDATE
- EXCLUDED holds the new values
- Needs a unique constraint or index
🔒
Chapter Quiz — Complete all 7 topics to unlock
0/7 topics done
Complete these topics first: