The jOOQ User Manual SQL building SQL Statements (DML) The INSERT statement INSERT .. ON DUPLICATE KEY

previous : next

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
)

The jOOQ User Manual SQL building SQL Statements (DML) The INSERT statement INSERT .. ON DUPLICATE KEY

previous : next