INSERT .. ON CONFLICT .. EXCLUDED
适用于 ✅ 开源版 ✅ 专业版 ✅ 企业版
INSERT .. ON CONFLICT 的一个特殊用例是 UPSERT 语句，其中所有（或大多数）要更新的值都是如果不存在冲突则会被插入的相同值 - 主键除外。 在 PostgreSQL 中，这可以使用 EXCLUDED 伪表来完成。 关键字应理解为“从语句的 INSERT 部分中排除的行”。
// Add a new author called "Koontz" with ID 3.
// If that ID is already present, update the author's name
create.insertInto(AUTHOR, AUTHOR.ID, AUTHOR.LAST_NAME)
.values(3, "Koontz")
.onConflict(AUTHOR.ID)
.doUpdate()
.set(AUTHOR.LAST_NAME, excluded(AUTHOR.LAST_NAME))
.execute();
请参阅 SET ALL TO EXCLUDED 以自动将所有列设置为 EXCLUDED。
方言支持
此示例使用 jOOQ
insertInto(AUTHOR, AUTHOR.ID, AUTHOR.LAST_NAME).values(3, "X").onConflict(AUTHOR.ID).doUpdate().set(AUTHOR.LAST_NAME, excluded(AUTHOR.LAST_NAME))
翻译成以下特定方言的表达式
Aurora MySQL, MariaDB, MemSQL
INSERT INTO AUTHOR (ID, LAST_NAME) VALUES ( 3, 'X' ) ON DUPLICATE KEY UPDATE AUTHOR.LAST_NAME = VALUES(LAST_NAME)
Aurora Postgres, CockroachDB, DuckDB, Postgres, SQLite, YugabyteDB
INSERT INTO AUTHOR (ID, LAST_NAME) VALUES ( 3, 'X' ) ON CONFLICT (ID) DO UPDATE SET LAST_NAME = excluded.LAST_NAME
DB2
MERGE INTO AUTHOR USING ( SELECT 3, 'X' FROM SYSIBM.DUAL ) t (ID, LAST_NAME) ON AUTHOR.ID = t.ID WHEN MATCHED THEN UPDATE SET AUTHOR.LAST_NAME = t.LAST_NAME WHEN NOT MATCHED THEN INSERT (ID, LAST_NAME) VALUES ( t.ID, t.LAST_NAME )
Derby
MERGE INTO AUTHOR USING SYSIBM.SYSDUMMY1 ON AUTHOR.ID = 3 WHEN MATCHED THEN UPDATE SET AUTHOR.LAST_NAME = 'X' WHEN NOT MATCHED THEN INSERT (ID, LAST_NAME) VALUES ( 3, 'X' )
Exasol
MERGE INTO AUTHOR USING ( SELECT 3, 'X' ) t (ID, LAST_NAME) ON AUTHOR.ID = t.ID WHEN MATCHED THEN UPDATE SET AUTHOR.LAST_NAME = t.LAST_NAME WHEN NOT MATCHED THEN INSERT (ID, LAST_NAME) VALUES ( t.ID, t.LAST_NAME )
Firebird
MERGE INTO AUTHOR USING ( SELECT 3, 'X' FROM RDB$DATABASE ) t (ID, LAST_NAME) ON AUTHOR.ID = t.ID WHEN MATCHED THEN UPDATE SET AUTHOR.LAST_NAME = t.LAST_NAME WHEN NOT MATCHED THEN INSERT (ID, LAST_NAME) VALUES ( t.ID, t.LAST_NAME )
H2
MERGE INTO AUTHOR
USING (
SELECT
3 ID,
'X' LAST_NAME
) t
ON AUTHOR.ID = t.ID
WHEN MATCHED THEN UPDATE SET
AUTHOR.LAST_NAME = t.LAST_NAME
WHEN NOT MATCHED THEN INSERT (ID, LAST_NAME)
VALUES (
t.ID,
t.LAST_NAME
)
Hana
MERGE INTO AUTHOR
USING (
(
SELECT
3 ID,
'X' LAST_NAME
FROM SYS.DUMMY
)
) t
ON AUTHOR.ID = t.ID
WHEN MATCHED THEN UPDATE SET
AUTHOR.LAST_NAME = t.LAST_NAME
WHEN NOT MATCHED THEN INSERT (ID, LAST_NAME)
VALUES (
t.ID,
t.LAST_NAME
)
HSQLDB
MERGE INTO AUTHOR USING ( SELECT 3, 'X' FROM (VALUES (1)) AS dual (dual) ) t (ID, LAST_NAME) ON AUTHOR.ID = t.ID WHEN MATCHED THEN UPDATE SET AUTHOR.LAST_NAME = t.LAST_NAME WHEN NOT MATCHED THEN INSERT (ID, LAST_NAME) VALUES ( t.ID, t.LAST_NAME )
MySQL
INSERT INTO AUTHOR (ID, LAST_NAME) VALUES ( 3, 'X' ) AS t ON DUPLICATE KEY UPDATE AUTHOR.LAST_NAME = t.LAST_NAME
Oracle
MERGE INTO AUTHOR
USING (
(
SELECT
3 ID,
'X' LAST_NAME
)
) t
ON (AUTHOR.ID = t.ID)
WHEN MATCHED THEN UPDATE SET
AUTHOR.LAST_NAME = t.LAST_NAME
WHEN NOT MATCHED THEN INSERT (ID, LAST_NAME)
VALUES (
t.ID,
t.LAST_NAME
)
Redshift, Snowflake
MERGE INTO AUTHOR USING ( SELECT 3, 'X' ) t (ID, LAST_NAME) ON AUTHOR.ID = t.ID WHEN MATCHED THEN UPDATE SET LAST_NAME = t.LAST_NAME WHEN NOT MATCHED THEN INSERT (ID, LAST_NAME) VALUES ( t.ID, t.LAST_NAME )
SQLServer
MERGE INTO AUTHOR USING ( SELECT 3, 'X' ) t (ID, LAST_NAME) ON AUTHOR.ID = t.ID WHEN MATCHED THEN UPDATE SET AUTHOR.LAST_NAME = t.LAST_NAME WHEN NOT MATCHED THEN INSERT (ID, LAST_NAME) VALUES ( t.ID, t.LAST_NAME );
Sybase
MERGE INTO AUTHOR USING ( SELECT 3, 'X' FROM SYS.DUMMY ) t (ID, LAST_NAME) ON AUTHOR.ID = t.ID WHEN MATCHED THEN UPDATE SET AUTHOR.LAST_NAME = t.LAST_NAME WHEN NOT MATCHED THEN INSERT (ID, LAST_NAME) VALUES ( t.ID, t.LAST_NAME )
Teradata
MERGE INTO AUTHOR
USING (
SELECT 3, 'X'
FROM (
SELECT 1 AS "dual"
) AS "dual"
) t (ID, LAST_NAME)
ON AUTHOR.ID = t.ID
WHEN MATCHED THEN UPDATE SET
LAST_NAME = t.LAST_NAME
WHEN NOT MATCHED THEN INSERT (ID, LAST_NAME)
VALUES (
t.ID,
t.LAST_NAME
)
ASE, Access, BigQuery, ClickHouse, Databricks, Informix, SQLDataWarehouse, Trino, Vertica
/* UNSUPPORTED */
使用 jOOQ 3.21 生成。早期 jOOQ 版本的支持可能有所不同。 在我们的网站上翻译您自己的 SQL
反馈
您对此页面有任何反馈吗？ 我们很乐意倾听！