INSERT .. ON CONFLICT .. SET ALL TO EXCLUDED
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
A synthetic SQL clause extension of the INSERT .. ON CONFLICT .. EXCLUDED syntax is a clause that sets all the (known) columns of a table to EXCLUDED
in the ON CONFLICT .. DO UPDATE
clause, for convenience.
// 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() .setAllToExcluded() .execute();
Dialect support
This example using jOOQ:
insertInto(AUTHOR, AUTHOR.ID, AUTHOR.LAST_NAME).values(3, "X").onConflict(AUTHOR.ID).doUpdate().setAllToExcluded()
Translates to the following dialect specific expressions:
Aurora Postgres, CockroachDB, DuckDB, Postgres, SQLite, YugabyteDB
INSERT INTO AUTHOR (ID, LAST_NAME) VALUES ( 3, 'X' ) ON CONFLICT (ID) DO UPDATE SET ID = excluded.ID, LAST_NAME = excluded.LAST_NAME
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.ID = t.ID, AUTHOR.LAST_NAME = t.LAST_NAME 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.ID = 3, 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.ID = t.ID, AUTHOR.LAST_NAME = t.LAST_NAME 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.ID = t.ID, AUTHOR.LAST_NAME = t.LAST_NAME 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.ID = t.ID, AUTHOR.LAST_NAME = t.LAST_NAME 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.ID = t.ID, AUTHOR.LAST_NAME = t.LAST_NAME 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.ID = t.ID, AUTHOR.LAST_NAME = t.LAST_NAME 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 (( SELECT AUTHOR.ID ) = t.ID) WHEN MATCHED THEN UPDATE SET AUTHOR.ID = t.ID, AUTHOR.LAST_NAME = t.LAST_NAME 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.ID = t.ID, AUTHOR.LAST_NAME = t.LAST_NAME 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.ID = t.ID, AUTHOR.LAST_NAME = t.LAST_NAME 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 ID = t.ID, LAST_NAME = t.LAST_NAME WHEN NOT MATCHED THEN INSERT (ID, LAST_NAME) VALUES ( t.ID, t.LAST_NAME )
ASE, Access, Aurora MySQL, BigQuery, ClickHouse, Informix, MariaDB, MemSQL, MySQL, Redshift, SQLDataWarehouse, Snowflake, Trino, Vertica
/* UNSUPPORTED */
Generated with jOOQ 3.20. Translate your own SQL on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!