UPDATE .. FROM
适用于 ✅ 开源版 ✅ 专业版 ✅ 企业版
一些数据库，包括例如 PostgreSQL 和 SQL Server，支持使用特定于供应商的 FROM 子句将附加表连接到 UPDATE 语句。 jOOQ 也支持这一点
UPDATE BOOK_ARCHIVE SET BOOK_ARCHIVE.TITLE = BOOK.TITLE FROM BOOK WHERE BOOK_ARCHIVE.ID = BOOK.ID
create.update(BOOK_ARCHIVE)
.set(BOOK_ARCHIVE.TITLE, BOOK.TITLE)
.from(BOOK)
.where(BOOK_ARCHIVE.ID.eq(BOOK.ID))
.execute();
在许多情况下，可以使用相关的子查询或可更新的视图来模拟这种连接的更新语句。 例如，大多数数据库都允许以某种方式在 UPDATE 语句中使用标量子查询。 为了方便起见，jOOQ 通过 UPDATE DSL API 中的 set(Field<T>, Select<? extends Record1<T>>) 方法来对此进行建模（有关更多详细信息，请参见关于标量子查询的部分）
UPDATE AUTHOR
SET FIRST_NAME = (
SELECT FIRST_NAME
FROM PERSON
WHERE PERSON.ID = AUTHOR.ID
),
WHERE ID = 3;
create.update(AUTHOR)
.set(AUTHOR.FIRST_NAME,
select(PERSON.FIRST_NAME)
.from(PERSON)
.where(PERSON.ID.eq(AUTHOR.ID))
)
.where(AUTHOR.ID.eq(3))
.execute();
方言支持
此示例使用 jOOQ
update(BOOK_TO_BOOK_STORE).set(BOOK_TO_BOOK_STORE.STOCK, 0).from(BOOK).where(BOOK_TO_BOOK_STORE.BOOK_ID.eq(BOOK.ID)).and(BOOK.AUTHOR_ID.eq(1))
翻译成以下特定方言的表达式
ASE, BigQuery, Oracle, SQLServer, Sybase
UPDATE BOOK_TO_BOOK_STORE SET BOOK_TO_BOOK_STORE.STOCK = 0 FROM BOOK WHERE ( BOOK_TO_BOOK_STORE.BOOK_ID = BOOK.ID AND BOOK.AUTHOR_ID = 1 )
Aurora MySQL, MariaDB, MemSQL, MySQL, Trino
UPDATE BOOK_TO_BOOK_STORE CROSS JOIN BOOK SET BOOK_TO_BOOK_STORE.STOCK = 0 WHERE ( BOOK_TO_BOOK_STORE.BOOK_ID = BOOK.ID AND BOOK.AUTHOR_ID = 1 )
Aurora Postgres, CockroachDB, DuckDB, Postgres, Redshift, SQLite, Snowflake, YugabyteDB
UPDATE BOOK_TO_BOOK_STORE SET STOCK = 0 FROM BOOK WHERE ( BOOK_TO_BOOK_STORE.BOOK_ID = BOOK.ID AND BOOK.AUTHOR_ID = 1 )
ClickHouse
UPDATE BOOK_TO_BOOK_STORE
SET
STOCK = 0
WHERE (NAME, BOOK_ID) IN (
SELECT BOOK_TO_BOOK_STORE.NAME, BOOK_TO_BOOK_STORE.BOOK_ID
FROM BOOK_TO_BOOK_STORE, BOOK
WHERE (
BOOK_TO_BOOK_STORE.BOOK_ID = BOOK.ID
AND BOOK.AUTHOR_ID = 1
)
)
Databricks
UPDATE BOOK_TO_BOOK_STORE
SET
STOCK = 0
WHERE EXISTS (
SELECT alias_1.v0, alias_1.v1
FROM (
SELECT
BOOK_TO_BOOK_STORE.NAME v0,
BOOK_TO_BOOK_STORE.BOOK_ID v1
FROM BOOK_TO_BOOK_STORE, BOOK
WHERE (
BOOK_TO_BOOK_STORE.BOOK_ID = BOOK.ID
AND BOOK.AUTHOR_ID = 1
)
) alias_1
WHERE (
coalesce(BOOK_TO_BOOK_STORE.NAME),
coalesce(BOOK_TO_BOOK_STORE.BOOK_ID)
) = (
coalesce(alias_1.v0),
coalesce(alias_1.v1)
)
)
DB2, Derby, Exasol, Firebird, H2, HSQLDB
MERGE INTO BOOK_TO_BOOK_STORE USING BOOK ON ( BOOK_TO_BOOK_STORE.BOOK_ID = BOOK.ID AND BOOK.AUTHOR_ID = 1 ) WHEN MATCHED THEN UPDATE SET BOOK_TO_BOOK_STORE.STOCK = 0
Hana
UPDATE BOOK_TO_BOOK_STORE FROM BOOK_TO_BOOK_STORE, BOOK SET BOOK_TO_BOOK_STORE.STOCK = 0 WHERE ( BOOK_TO_BOOK_STORE.BOOK_ID = BOOK.ID AND BOOK.AUTHOR_ID = 1 )
SQLDataWarehouse
UPDATE BOOK_TO_BOOK_STORE
SET
BOOK_TO_BOOK_STORE.STOCK = 0
WHERE EXISTS (
SELECT alias_1.v0, alias_1.v1
FROM (
SELECT
BOOK_TO_BOOK_STORE.NAME v0,
BOOK_TO_BOOK_STORE.BOOK_ID v1
FROM BOOK_TO_BOOK_STORE, BOOK
WHERE (
BOOK_TO_BOOK_STORE.BOOK_ID = BOOK.ID
AND BOOK.AUTHOR_ID = 1
)
) alias_1
WHERE (
BOOK_TO_BOOK_STORE.NAME = alias_1.v0
AND BOOK_TO_BOOK_STORE.BOOK_ID = alias_1.v1
)
)
Teradata, Vertica
MERGE INTO BOOK_TO_BOOK_STORE USING BOOK ON ( BOOK_TO_BOOK_STORE.BOOK_ID = BOOK.ID AND BOOK.AUTHOR_ID = 1 ) WHEN MATCHED THEN UPDATE SET STOCK = 0
Access, Informix
/* UNSUPPORTED */
使用 jOOQ 3.21 生成。早期 jOOQ 版本的支持可能有所不同。 在我们的网站上翻译您自己的 SQL
反馈
您对此页面有任何反馈吗？ 我们很乐意听到您的反馈！