This documentation is for the unreleased development version of jOOQ. Click on the above version links to get this documentation for a supported version of jOOQ.
WHEN MATCHED AND ..
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
Various dialects support the standard SQL syntax for having multiple WHEN clauses, where an additional predicates are supplied with each clause.
This works in a similar way to a CASE expression, where the first matching CASE gets applied.
If the syntax isn't supported in a dialect, it can be emulated by collapsing the various WHEN clauses into a single one that uses a CASE expression to cover all the conditions and results.
Dialect support
This example using jOOQ:
mergeInto(AUTHOR)
.using(selectOne())
.on(AUTHOR.LAST_NAME.eq("Hitchcock"))
.whenMatchedAnd(AUTHOR.FIRST_NAME.eq("Mary"))
.thenUpdate().set(AUTHOR.YEAR_OF_BIRTH, 1849)
.whenMatchedAnd(AUTHOR.FIRST_NAME.eq("Alfred"))
.thenUpdate().set(AUTHOR.YEAR_OF_BIRTH, 1899)
Translates to the following dialect specific expressions:
Databricks, Snowflake
MERGE INTO AUTHOR USING ( SELECT 1 one ) ON AUTHOR.LAST_NAME = 'Hitchcock' WHEN MATCHED AND AUTHOR.FIRST_NAME = 'Mary' THEN UPDATE SET YEAR_OF_BIRTH = 1849 WHEN MATCHED AND AUTHOR.FIRST_NAME = 'Alfred' THEN UPDATE SET YEAR_OF_BIRTH = 1899
DB2
MERGE INTO AUTHOR USING ( SELECT 1 one FROM SYSIBM.DUAL ) ON AUTHOR.LAST_NAME = 'Hitchcock' WHEN MATCHED AND AUTHOR.FIRST_NAME = 'Mary' THEN UPDATE SET AUTHOR.YEAR_OF_BIRTH = 1849 WHEN MATCHED AND AUTHOR.FIRST_NAME = 'Alfred' THEN UPDATE SET AUTHOR.YEAR_OF_BIRTH = 1899
Derby
MERGE INTO AUTHOR USING ( SELECT 1 one FROM SYSIBM.SYSDUMMY1 ) ON AUTHOR.LAST_NAME = 'Hitchcock' WHEN MATCHED AND AUTHOR.FIRST_NAME = 'Mary' THEN UPDATE SET AUTHOR.YEAR_OF_BIRTH = 1849 WHEN MATCHED AND AUTHOR.FIRST_NAME = 'Alfred' THEN UPDATE SET AUTHOR.YEAR_OF_BIRTH = 1899
Exasol
MERGE INTO AUTHOR
USING (
SELECT 1 one
)
ON AUTHOR.LAST_NAME = 'Hitchcock'
WHEN MATCHED THEN UPDATE SET
AUTHOR.YEAR_OF_BIRTH = CASE
WHEN AUTHOR.FIRST_NAME = 'Mary' THEN 1849
WHEN AUTHOR.FIRST_NAME = 'Alfred' THEN 1899
ELSE AUTHOR.YEAR_OF_BIRTH
END
WHERE (
AUTHOR.FIRST_NAME = 'Mary'
OR AUTHOR.FIRST_NAME = 'Alfred'
)
Firebird
MERGE INTO AUTHOR USING ( SELECT 1 one FROM RDB$DATABASE ) ON AUTHOR.LAST_NAME = 'Hitchcock' WHEN MATCHED AND AUTHOR.FIRST_NAME = 'Mary' THEN UPDATE SET AUTHOR.YEAR_OF_BIRTH = 1849 WHEN MATCHED AND AUTHOR.FIRST_NAME = 'Alfred' THEN UPDATE SET AUTHOR.YEAR_OF_BIRTH = 1899
H2
MERGE INTO AUTHOR USING ( SELECT 1 one ) ON AUTHOR.LAST_NAME = 'Hitchcock' WHEN MATCHED AND AUTHOR.FIRST_NAME = 'Mary' THEN UPDATE SET AUTHOR.YEAR_OF_BIRTH = 1849 WHEN MATCHED AND ( NOT (AUTHOR.FIRST_NAME = 'Mary') AND AUTHOR.FIRST_NAME = 'Alfred' ) THEN UPDATE SET AUTHOR.YEAR_OF_BIRTH = 1899
Hana
MERGE INTO AUTHOR
USING (
SELECT 1 one
FROM SYS.DUMMY
)
ON AUTHOR.LAST_NAME = 'Hitchcock'
WHEN MATCHED AND (
AUTHOR.FIRST_NAME = 'Mary'
OR AUTHOR.FIRST_NAME = 'Alfred'
) THEN UPDATE SET
AUTHOR.YEAR_OF_BIRTH = CASE
WHEN AUTHOR.FIRST_NAME = 'Mary' THEN 1849
WHEN AUTHOR.FIRST_NAME = 'Alfred' THEN 1899
ELSE AUTHOR.YEAR_OF_BIRTH
END
HSQLDB
MERGE INTO AUTHOR
USING (
SELECT 1 one
FROM (VALUES (1)) AS dual (dual)
)
ON AUTHOR.LAST_NAME = 'Hitchcock'
WHEN MATCHED AND (
AUTHOR.FIRST_NAME = 'Mary'
OR (
NOT (AUTHOR.FIRST_NAME = 'Mary')
AND AUTHOR.FIRST_NAME = 'Alfred'
)
) THEN UPDATE SET
AUTHOR.YEAR_OF_BIRTH = CASE
WHEN AUTHOR.FIRST_NAME = 'Mary' THEN 1849
WHEN (
NOT (AUTHOR.FIRST_NAME = 'Mary')
AND NOT (AUTHOR.FIRST_NAME = 'Mary')
AND AUTHOR.FIRST_NAME = 'Alfred'
) THEN 1899
ELSE AUTHOR.YEAR_OF_BIRTH
END
Oracle
MERGE INTO AUTHOR
USING (
SELECT 1 one
)
ON (AUTHOR.LAST_NAME = 'Hitchcock')
WHEN MATCHED THEN UPDATE SET
AUTHOR.YEAR_OF_BIRTH = CASE
WHEN AUTHOR.FIRST_NAME = 'Mary' THEN 1849
WHEN AUTHOR.FIRST_NAME = 'Alfred' THEN 1899
ELSE AUTHOR.YEAR_OF_BIRTH
END
WHERE (
AUTHOR.FIRST_NAME = 'Mary'
OR AUTHOR.FIRST_NAME = 'Alfred'
)
Postgres, Vertica
MERGE INTO AUTHOR
USING (
SELECT 1 one
) AS dummy_30260683("one")
ON AUTHOR.LAST_NAME = 'Hitchcock'
WHEN MATCHED AND AUTHOR.FIRST_NAME = 'Mary' THEN UPDATE SET
YEAR_OF_BIRTH = 1849
WHEN MATCHED AND AUTHOR.FIRST_NAME = 'Alfred' THEN UPDATE SET
YEAR_OF_BIRTH = 1899
SQLServer
MERGE INTO AUTHOR
USING (
SELECT 1 one
) AS dummy_30260683([one])
ON AUTHOR.LAST_NAME = 'Hitchcock'
WHEN MATCHED AND (
AUTHOR.FIRST_NAME = 'Mary'
OR (
NOT (AUTHOR.FIRST_NAME = 'Mary')
AND AUTHOR.FIRST_NAME = 'Alfred'
)
) THEN UPDATE SET
AUTHOR.YEAR_OF_BIRTH = CASE
WHEN AUTHOR.FIRST_NAME = 'Mary' THEN 1849
WHEN (
NOT (AUTHOR.FIRST_NAME = 'Mary')
AND NOT (AUTHOR.FIRST_NAME = 'Mary')
AND AUTHOR.FIRST_NAME = 'Alfred'
) THEN 1899
ELSE AUTHOR.YEAR_OF_BIRTH
END;
Sybase
MERGE INTO AUTHOR USING ( SELECT 1 one FROM SYS.DUMMY ) AS dummy_30260683([one]) ON AUTHOR.LAST_NAME = 'Hitchcock' WHEN MATCHED AND AUTHOR.FIRST_NAME = 'Mary' THEN UPDATE SET AUTHOR.YEAR_OF_BIRTH = 1849 WHEN MATCHED AND AUTHOR.FIRST_NAME = 'Alfred' THEN UPDATE SET AUTHOR.YEAR_OF_BIRTH = 1899
Teradata
MERGE INTO AUTHOR
USING (
SELECT 1 one
FROM (
SELECT 1 AS "dual"
) AS "dual"
)
ON AUTHOR.LAST_NAME = 'Hitchcock'
WHEN MATCHED THEN UPDATE SET
YEAR_OF_BIRTH = CASE
WHEN AUTHOR.FIRST_NAME = 'Mary' THEN 1849
WHEN AUTHOR.FIRST_NAME = 'Alfred' THEN 1899
ELSE AUTHOR.YEAR_OF_BIRTH
END
WHERE (
AUTHOR.FIRST_NAME = 'Mary'
OR AUTHOR.FIRST_NAME = 'Alfred'
)
ASE, Access, Aurora MySQL, Aurora Postgres, BigQuery, ClickHouse, CockroachDB, DuckDB, Informix, MariaDB, MemSQL, MySQL, Redshift, SQLDataWarehouse, SQLite, Trino, YugabyteDB
/* UNSUPPORTED */
Generated with jOOQ 3.21. Translate your own SQL on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!