Available in versions: Dev (3.19) | Latest (3.18) | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10 | 3.9
INSERT .. ON DUPLICATE KEY
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
The synthetic ON DUPLICATE KEY UPDATE clause
The MySQL database supports a very convenient way to INSERT or UPDATE a record. This is a non-standard extension to the SQL syntax, which is supported by jOOQ and emulated in other RDBMS, where this is possible (e.g. if they support the SQL standard MERGE statement). Here is an example how to use the ON DUPLICATE KEY UPDATE clause:
// 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") .onDuplicateKeyUpdate() .set(AUTHOR.LAST_NAME, "Koontz") .execute();
The synthetic ON DUPLICATE KEY IGNORE clause
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:
Emulating IGNORE with MERGE
The above jOOQ statement can be emulated with the following, equivalent SQL statement:
MERGE INTO AUTHOR USING (SELECT 1 FROM DUAL) ON (AUTHOR.ID = 3) WHEN NOT MATCHED THEN INSERT (ID, LAST_NAME) VALUES (3, 'Koontz')
Emulating IGNORE with INSERT .. SELECT WHERE NOT EXISTS
The above jOOQ statement can be emulated with the following, equivalent SQL statement:
INSERT INTO AUTHOR (ID, LAST_NAME) SELECT 3, 'Koontz' WHERE NOT EXISTS ( SELECT 1 FROM AUTHOR WHERE AUTHOR.ID = 3 )
Feedback
Do you have any feedback about this page? We'd love to hear it!