WHEN MATCHED AND ..
Supported by ✅ 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
UPDATE AUTHOR 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 FROM ( SELECT 1 one ) alias_30260683 WHERE ( AUTHOR.LAST_NAME = 'Hitchcock' AND ( 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, Spanner, 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!