Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10
The MERGE statement
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
The MERGE statement is one of the most advanced standardised SQL constructs, which is supported by DB2, HSQLDB, Oracle, SQL Server and Sybase (MySQL has the similar INSERT .. ON DUPLICATE KEY UPDATE construct)
The point of the standard MERGE statement is to take a TARGET table, and merge (INSERT, UPDATE) data from a SOURCE table into it. DB2, Oracle, SQL Server and Sybase also allow for DELETING some data and for adding many additional clauses. With jOOQ 3.13.6, only Oracle's MERGE extensions are supported. Here is an example:
-- Check if there is already an author called 'Hitchcock'
-- If there is, rename him to John. If there isn't add him.
MERGE INTO AUTHOR
USING (SELECT 1 FROM DUAL)
ON (LAST_NAME = 'Hitchcock')
WHEN MATCHED THEN UPDATE SET FIRST_NAME = 'John'
WHEN NOT MATCHED THEN INSERT (LAST_NAME) VALUES ('Hitchcock');
create.mergeInto(AUTHOR)
.using(create.selectOne())
.on(AUTHOR.LAST_NAME.eq("Hitchcock"))
.whenMatchedThenUpdate()
.set(AUTHOR.FIRST_NAME, "John")
.whenNotMatchedThenInsert(AUTHOR.LAST_NAME)
.values("Hitchcock")
.execute();
MERGE Statement (H2-specific syntax)
The H2 database ships with a somewhat less powerful but a little more intuitive syntax for its own version of the MERGE statement. An example more or less equivalent to the previous one can be seen here:
-- Check if there is already an author called 'Hitchcock'
-- If there is, rename him to John. If there isn't add him.
MERGE INTO AUTHOR (FIRST_NAME, LAST_NAME)
KEY (LAST_NAME)
VALUES ('John', 'Hitchcock')
create.mergeInto(AUTHOR,
AUTHOR.FIRST_NAME,
AUTHOR.LAST_NAME)
.key(AUTHOR.LAST_NAME)
.values("John", "Hitchcock")
.execute();
This syntax can be fully emulated by jOOQ for all other databases that support the SQL standard MERGE statement. For more information about the H2 MERGE syntax, see the documentation here:
https://www.h2database.com/html/grammar.html#merge
Typesafety of VALUES() for degrees up to 22
Much like the INSERT statement, the MERGE statement's VALUES() clause provides typesafety for degrees up to 22, in both the standard syntax variant as well as the H2 variant.
Feedback
Do you have any feedback about this page? We'd love to hear it!