Insert rows that don't exist and update those that do in one statement.
Insert a row if it does not exist, or update it if it does. PostgreSQL uses INSERT ... ON CONFLICT DO UPDATE; MySQL uses INSERT ... ON DUPLICATE KEY UPDATE; SQL Server uses MERGE. Essential for idempotent data ingestion pipelines.
Compatibility
| Database System | Support Status | Since Version | Notes |
|---|---|---|---|
| MySQL | Native | 5.1 | ON DUPLICATE KEY UPDATE |
| PostgreSQL | Native | 9.5 | ON CONFLICT DO UPDATE |
| SQL Server | Native | 2008 | Via MERGE |
| Oracle | Native | 9i | Via MERGE |
| SQLite | Native | 3.24.0 | INSERT…ON CONFLICT |
Details
Atomic insert-or-update in one shot.
Standard Syntax
INSERT INTO tbl (col1,col2)
VALUES (v1,v2)
ON CONFLICT (pk) DO UPDATE
SET col1 = EXCLUDED.col1;
Version Support
MySQL: Native since 5.1
PostgreSQL: Native since 9.5
SQL Server: Native since 2008
Oracle: Native since 9i
SQLite: Native since 3.24.0
Per-Database Syntax & Notes
MySQL Native syntax
ON DUPLICATE KEY UPDATE
INSERT INTO tbl (…) VALUES (…) ON DUPLICATE KEY UPDATE …;
PostgreSQL Native syntax
ON CONFLICT DO UPDATE
INSERT INTO tbl (…) VALUES (…) ON CONFLICT(pk) DO UPDATE SET …;
SQL Server Native syntax
Via MERGE
MERGE … WHEN MATCHED THEN UPDATE WHEN NOT MATCHED THEN INSERT;
Oracle Native syntax
Via MERGE
MERGE … WHEN MATCHED THEN UPDATE WHEN NOT MATCHED THEN INSERT;
SQLite Native syntax
INSERT…ON CONFLICT
INSERT INTO tbl (…) VALUES (…) ON CONFLICT(pk) DO UPDATE SET …;