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.15.12, 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();
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!