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 DUPLICATE KEY IGNORE
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
The MySQL database also supports an INSERT IGNORE INTO clause. This is supported by jOOQ using the more convenient SQL syntax variant of ON DUPLICATE KEY IGNORE:
// Add a new author called "Koontz" with ID 3.
// If that ID is already present, ignore the INSERT statement
create.insertInto(AUTHOR, AUTHOR.ID, AUTHOR.LAST_NAME)
.values(3, "Koontz")
.onDuplicateKeyIgnore()
.execute();
If the underlying database doesn't have any way to "ignore" failing INSERT statements, (e.g. MySQL via INSERT IGNORE), jOOQ can emulate the statement using a MERGE statement, or using INSERT .. SELECT WHERE NOT EXISTS.
The MySQL INSERT IGNORE statement ignores more constraint violations than just duplicate keys, so the emulation isn't exactly equivalent, see #5211
Dialect support
This example using jOOQ:
insertInto(AUTHOR, AUTHOR.ID, AUTHOR.LAST_NAME).values(3, "X").onDuplicateKeyIgnore()
Translates to the following dialect specific expressions:
Access
INSERT INTO AUTHOR (ID, LAST_NAME)
SELECT t.v0, t.v1
FROM (
SELECT
3 v0,
'X' v1
FROM (
SELECT count(*) dual
FROM MSysResources
) AS dual
WHERE NOT EXISTS (
SELECT 1
FROM AUTHOR
WHERE AUTHOR.ID = 3
)
) t
ASE, ClickHouse, SQLDataWarehouse, Trino, Vertica
INSERT INTO AUTHOR (ID, LAST_NAME)
SELECT t.v0, t.v1
FROM (
SELECT
3 v0,
'X' v1
WHERE NOT EXISTS (
SELECT 1
FROM AUTHOR
WHERE AUTHOR.ID = 3
)
) t
Aurora MySQL, MariaDB, MySQL
INSERT IGNORE INTO AUTHOR (ID, LAST_NAME) VALUES ( 3, 'X' )
Aurora Postgres, CockroachDB, DuckDB, Postgres, SQLite, YugabyteDB
INSERT INTO AUTHOR (ID, LAST_NAME) VALUES ( 3, 'X' ) ON CONFLICT DO NOTHING
BigQuery
INSERT INTO AUTHOR (ID, LAST_NAME)
SELECT t.v0, t.v1
FROM (
SELECT
3 v0,
'X' v1
FROM UNNEST([STRUCT(1 AS dual)]) AS dual
WHERE NOT EXISTS (
SELECT 1
FROM AUTHOR
WHERE AUTHOR.ID = 3
)
) t
Databricks, Redshift, Snowflake
MERGE INTO AUTHOR USING ( SELECT 3, 'X' ) t (ID, LAST_NAME) ON AUTHOR.ID = t.ID WHEN NOT MATCHED THEN INSERT (ID, LAST_NAME) VALUES ( t.ID, t.LAST_NAME )
DB2
MERGE INTO AUTHOR USING ( SELECT 3, 'X' FROM SYSIBM.DUAL ) t (ID, LAST_NAME) ON AUTHOR.ID = t.ID 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 NOT MATCHED THEN INSERT (ID, LAST_NAME) VALUES ( 3, 'X' )
Exasol, MemSQL
INSERT INTO AUTHOR (ID, LAST_NAME)
SELECT t.v0, t.v1
FROM (
SELECT
3 v0,
'X' v1
FROM DUAL
WHERE NOT EXISTS (
SELECT 1
FROM AUTHOR
WHERE AUTHOR.ID = 3
)
) t
Firebird
MERGE INTO AUTHOR USING ( SELECT 3, 'X' FROM RDB$DATABASE ) t (ID, LAST_NAME) ON AUTHOR.ID = t.ID 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 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 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 NOT MATCHED THEN INSERT (ID, LAST_NAME) VALUES ( t.ID, t.LAST_NAME )
Informix
INSERT INTO AUTHOR (ID, LAST_NAME)
SELECT t.v0, t.v1
FROM (
SELECT
3 v0,
'X' v1
FROM (
SELECT 1 AS dual
FROM systables
WHERE (tabid = 1)
) AS dual
WHERE NOT EXISTS (
SELECT 1
FROM AUTHOR
WHERE AUTHOR.ID = 3
)
) t
Oracle
MERGE INTO AUTHOR
USING (
(
SELECT
3 ID,
'X' LAST_NAME
)
) t
ON (AUTHOR.ID = t.ID)
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 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 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 NOT MATCHED THEN INSERT (ID, LAST_NAME)
VALUES (
t.ID,
t.LAST_NAME
)
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!