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
Redshift
MERGE INTO AUTHOR
USING (
SELECT 1 one
)
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'
)
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, SQLDataWarehouse, SQLite, Trino, YugabyteDB
/* UNSUPPORTED */
Generated with jOOQ 3.21. Support in older jOOQ versions may differ. Translate your own SQL on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!