UPSERTDML
Buy Me A Coffee

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

Show:
Database System Support Status Since Version Notes
MySQL ✓ Supported 5.1 ON DUPLICATE KEY UPDATE
PostgreSQL ✓ Supported 9.5 ON CONFLICT DO UPDATE
SQL Server ✓ Supported 2008 Via MERGE
Oracle ✓ Supported 9i Via MERGE
SQLite ✓ Supported 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: Since 5.1 PostgreSQL: Since 9.5 SQL Server: Since 2008 Oracle: Since 9i SQLite: Since 3.24.0

Per-Database Syntax & Notes

MySQL

ON DUPLICATE KEY UPDATE

INSERT INTO tbl (…) VALUES (…) ON DUPLICATE KEY UPDATE …;

PostgreSQL

ON CONFLICT DO UPDATE

INSERT INTO tbl (…) VALUES (…) ON CONFLICT(pk) DO UPDATE SET …;

SQL Server

Via MERGE

MERGE … WHEN MATCHED THEN UPDATE WHEN NOT MATCHED THEN INSERT;

Oracle

Via MERGE

MERGE … WHEN MATCHED THEN UPDATE WHEN NOT MATCHED THEN INSERT;

SQLite

INSERT…ON CONFLICT

INSERT INTO tbl (…) VALUES (…) ON CONFLICT(pk) DO UPDATE SET …;