Performs insert, update, or delete operations on a target table based on a join to a source table.
The ANSI SQL standard upsert command that conditionally INSERTs, UPDATE, or DELETE rows based on a source dataset matching a target table. Not natively supported by MySQL; use INSERT ... ON DUPLICATE KEY UPDATE or MySQL 8+ MERGE workaround.
Compatibility
| Database System | Support Status | Since Version | Notes |
|---|---|---|---|
| MySQL | Not Supported | - | No MERGE—use INSERT…ON DUPLICATE KEY UPDATE |
| PostgreSQL | Native | 15 | Native MERGE support since PostgreSQL 15 Prior to this version, use WITH upsert AS ( UPDATE t SET … WHERE … RETURNING * ) INSERT INTO t (…) SELECT … WHERE NOT EXISTS (SELECT 1 FROM upsert); |
| SQL Server | Native | 2008 | Available since SQL Server 2008 |
| Oracle | Native | 9i | Available since Oracle 9i |
| SQLite | Not Supported | - | No MERGE—use INSERT…ON CONFLICT |
Details
Useful for ETL and slowly changing dimensions.
Standard Syntax
MERGE INTO target_table
USING source_table
ON (target.pk = source.pk)
WHEN MATCHED THEN
UPDATE SET …
WHEN NOT MATCHED THEN
INSERT (…) VALUES (…);
Version Support
MySQL: Not supported
PostgreSQL: Native since 15
SQL Server: Native since 2008
Oracle: Native since 9i
SQLite: Not supported
Per-Database Syntax & Notes
MySQL Alternative syntax
No MERGE—use INSERT…ON DUPLICATE KEY UPDATE
INSERT INTO t (…) VALUES (…) ON DUPLICATE KEY UPDATE …;
PostgreSQL Native syntax
Native MERGE support since PostgreSQL 15 Prior to this version, use WITH upsert AS ( UPDATE t SET … WHERE … RETURNING * ) INSERT INTO t (…) SELECT … WHERE NOT EXISTS (SELECT 1 FROM upsert);
MERGE INTO target_table
USING source_table
ON (target.pk = source.pk)
WHEN MATCHED THEN
UPDATE SET …
WHEN NOT MATCHED THEN
INSERT (…) VALUES (…);
SQL Server Native syntax
Available since SQL Server 2008
MERGE INTO target AS T
USING source AS S
ON T.pk = S.pk
WHEN MATCHED THEN UPDATE SET …
WHEN NOT MATCHED THEN INSERT (… ) VALUES (… );
Oracle Native syntax
Available since Oracle 9i
MERGE INTO target T
USING source S
ON (T.pk = S.pk)
WHEN MATCHED THEN UPDATE SET …
WHEN NOT MATCHED THEN INSERT (…) VALUES (…);
SQLite Alternative syntax
No MERGE—use INSERT…ON CONFLICT
INSERT INTO t (…) VALUES (…) ON CONFLICT(pk) DO UPDATE SET …;