Available in versions: Dev (3.21) | Latest (3.20) | 3.19 | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11
INSERT .. ON CONFLICT
Supported by ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
The PostgreSQL database offers an alternative syntax to MySQL's vendor specific INSERT .. ON DUPLICATE KEY syntax, which allows for specifying an explicit (reference by constraint name) or implicit (reference by column list) unique constraint for conflict resolution.
// Add a new author called "Koontz" with ID 3. // If that ID is already present, update the author's name create.insertInto(AUTHOR, AUTHOR.ID, AUTHOR.LAST_NAME) .values(3, "Koontz") .onConflict(AUTHOR.ID) .doUpdate() .set(AUTHOR.LAST_NAME, "Koontz") .execute();
Dialect support
This example using jOOQ:
insertInto(AUTHOR, AUTHOR.ID, AUTHOR.LAST_NAME).values(3, "X").onConflict(AUTHOR.ID).doUpdate().set(AUTHOR.LAST_NAME, "X")
Translates to the following dialect specific expressions:
Aurora MySQL, MariaDB, MemSQL, MySQL
INSERT INTO AUTHOR (ID, LAST_NAME) VALUES ( 3, 'X' ) ON DUPLICATE KEY UPDATE AUTHOR.LAST_NAME = 'X'
Aurora Postgres, CockroachDB, DuckDB, Postgres, SQLite, YugabyteDB
INSERT INTO AUTHOR (ID, LAST_NAME) VALUES ( 3, 'X' ) ON CONFLICT (ID) DO UPDATE SET LAST_NAME = 'X'
DB2
MERGE INTO AUTHOR USING ( SELECT 3, 'X' FROM SYSIBM.DUAL ) t (ID, LAST_NAME) ON AUTHOR.ID = t.ID WHEN MATCHED THEN UPDATE SET AUTHOR.LAST_NAME = 'X' WHEN NOT MATCHED THEN INSERT (ID, LAST_NAME) VALUES ( t.ID, t.LAST_NAME )
Derby
MERGE INTO AUTHOR USING SYSIBM.SYSDUMMY1 ON AUTHOR.ID = 3 WHEN MATCHED THEN UPDATE SET AUTHOR.LAST_NAME = 'X' WHEN NOT MATCHED THEN INSERT (ID, LAST_NAME) VALUES ( 3, 'X' )
Exasol
MERGE INTO AUTHOR USING ( SELECT 3, 'X' ) t (ID, LAST_NAME) ON AUTHOR.ID = t.ID WHEN MATCHED THEN UPDATE SET AUTHOR.LAST_NAME = 'X' WHEN NOT MATCHED THEN INSERT (ID, LAST_NAME) VALUES ( t.ID, t.LAST_NAME )
Firebird
MERGE INTO AUTHOR USING ( SELECT 3, 'X' FROM RDB$DATABASE ) t (ID, LAST_NAME) ON AUTHOR.ID = t.ID WHEN MATCHED THEN UPDATE SET AUTHOR.LAST_NAME = 'X' WHEN NOT MATCHED THEN INSERT (ID, LAST_NAME) VALUES ( t.ID, t.LAST_NAME )
H2
MERGE INTO AUTHOR USING ( SELECT 3 ID, 'X' LAST_NAME ) t ON AUTHOR.ID = t.ID WHEN MATCHED THEN UPDATE SET AUTHOR.LAST_NAME = 'X' WHEN NOT MATCHED THEN INSERT (ID, LAST_NAME) VALUES ( t.ID, t.LAST_NAME )
Hana
MERGE INTO AUTHOR USING ( ( SELECT 3 ID, 'X' LAST_NAME FROM SYS.DUMMY ) ) t ON AUTHOR.ID = t.ID WHEN MATCHED THEN UPDATE SET AUTHOR.LAST_NAME = 'X' WHEN NOT MATCHED THEN INSERT (ID, LAST_NAME) VALUES ( t.ID, t.LAST_NAME )
HSQLDB
MERGE INTO AUTHOR USING ( SELECT 3, 'X' FROM (VALUES (1)) AS dual (dual) ) t (ID, LAST_NAME) ON AUTHOR.ID = t.ID WHEN MATCHED THEN UPDATE SET AUTHOR.LAST_NAME = 'X' WHEN NOT MATCHED THEN INSERT (ID, LAST_NAME) VALUES ( t.ID, t.LAST_NAME )
Oracle
MERGE INTO AUTHOR USING ( ( SELECT 3 ID, 'X' LAST_NAME ) ) t ON (AUTHOR.ID = t.ID) WHEN MATCHED THEN UPDATE SET AUTHOR.LAST_NAME = 'X' WHEN NOT MATCHED THEN INSERT (ID, LAST_NAME) VALUES ( t.ID, t.LAST_NAME )
Redshift, Snowflake
MERGE INTO AUTHOR USING ( SELECT 3, 'X' ) t (ID, LAST_NAME) ON AUTHOR.ID = t.ID WHEN MATCHED THEN UPDATE SET LAST_NAME = 'X' WHEN NOT MATCHED THEN INSERT (ID, LAST_NAME) VALUES ( t.ID, t.LAST_NAME )
SQLServer
MERGE INTO AUTHOR USING ( SELECT 3, 'X' ) t (ID, LAST_NAME) ON AUTHOR.ID = t.ID WHEN MATCHED THEN UPDATE SET AUTHOR.LAST_NAME = 'X' WHEN NOT MATCHED THEN INSERT (ID, LAST_NAME) VALUES ( t.ID, t.LAST_NAME );
Sybase
MERGE INTO AUTHOR USING ( SELECT 3, 'X' FROM SYS.DUMMY ) t (ID, LAST_NAME) ON AUTHOR.ID = t.ID WHEN MATCHED THEN UPDATE SET AUTHOR.LAST_NAME = 'X' WHEN NOT MATCHED THEN INSERT (ID, LAST_NAME) VALUES ( t.ID, t.LAST_NAME )
Teradata
MERGE INTO AUTHOR USING ( SELECT 3, 'X' FROM ( SELECT 1 AS "dual" ) AS "dual" ) t (ID, LAST_NAME) ON AUTHOR.ID = t.ID WHEN MATCHED THEN UPDATE SET LAST_NAME = 'X' WHEN NOT MATCHED THEN INSERT (ID, LAST_NAME) VALUES ( t.ID, t.LAST_NAME )
ASE, Access, BigQuery, ClickHouse, Databricks, Informix, SQLDataWarehouse, Spanner, Trino, Vertica
/* UNSUPPORTED */
Generated with jOOQ 3.21. Support in older jOOQ versions may differ. Translate your own SQL on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!