WHEN MATCHED AND ..
适用于 ✅ 开源版 ✅ 专业版 ✅ 企业版
各种方言支持标准 SQL 语法，其中包含多个 WHEN 子句，并且每个子句都提供了额外的 谓词。
它的工作方式类似于 CASE 表达式，其中应用第一个匹配的 CASE。
如果某个方言不支持此语法，则可以通过将各种 WHEN 子句折叠为单个子句来模拟它，该子句使用 CASE 表达式 来涵盖所有条件和结果。
方言支持
此示例使用 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)
翻译成以下特定方言的表达式
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, Trino, YugabyteDB
/* UNSUPPORTED */
使用 jOOQ 3.21 生成。早期 jOOQ 版本的支持可能有所不同。 在我们的网站上翻译您自己的 SQL
反馈
您对此页面有任何反馈吗？ 我们很乐意听取您的意见!