UPDATE .. SET ROW
适用于 ✅ 开源版 ✅ 专业版 ✅ 企业版
SET 子句允许在表的更新记录中设置 行值表达式。
UPDATE AUTHOR
SET (FIRST_NAME, LAST_NAME) =
('Hermann', 'Hesse')
WHERE ID = 3;
create.update(AUTHOR)
.set(row(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME),
row("Herman", "Hesse"))
.where(AUTHOR.ID.eq(3))
.execute();
这在使用 `SET` 子句中的相关子查询时可能特别有用，在这种情况下，可以使用单个子查询更新多个列，而不仅仅是 1 个。有关此场景的替代语法，请参阅 UPDATE .. FROM。
UPDATE AUTHOR
SET (FIRST_NAME, LAST_NAME) = (
SELECT PERSON.FIRST_NAME, PERSON.LAST_NAME
FROM PERSON
WHERE PERSON.ID = AUTHOR.ID
)
WHERE ID = 3;
create.update(AUTHOR)
.set(row(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME),
select(PERSON.FIRST_NAME, PERSON.LAST_NAME)
.from(PERSON)
.where(PERSON.ID.eq(AUTHOR.ID))
)
.where(AUTHOR.ID.eq(3))
.execute();
update(BOOK).set(row(BOOK.TITLE, BOOK.LANGUAGE_ID), row("New Title", 1))
翻译成以下特定方言的表达式
ASE, Access, Aurora MySQL, Derby, Exasol, Firebird, Informix, MariaDB, MemSQL, MySQL, SQLDataWarehouse, SQLServer, Sybase
UPDATE BOOK SET BOOK.TITLE = 'New Title', BOOK.LANGUAGE_ID = 1
Aurora Postgres, CockroachDB, DB2, H2, HSQLDB, Trino
UPDATE BOOK
SET
(TITLE, LANGUAGE_ID) = ('New Title', 1)
BigQuery
UPDATE BOOK SET BOOK.TITLE = 'New Title', BOOK.LANGUAGE_ID = 1 WHERE TRUE
ClickHouse
UPDATE BOOK SET TITLE = 'New Title', LANGUAGE_ID = 1 WHERE TRUE
Databricks, DuckDB, Redshift, SQLite, Snowflake, Teradata, Vertica
UPDATE BOOK SET TITLE = 'New Title', LANGUAGE_ID = 1
Hana
UPDATE BOOK
FROM BOOK
SET
(TITLE, LANGUAGE_ID) = ('New Title', 1)
Oracle
UPDATE BOOK
SET
(TITLE, LANGUAGE_ID) = (
SELECT 'New Title', 1
)
Postgres, YugabyteDB
UPDATE BOOK
SET
(TITLE, LANGUAGE_ID) = ROW ('New Title', 1)
使用 jOOQ 3.21 生成。早期 jOOQ 版本的支持可能有所不同。 在我们的网站上翻译您自己的 SQL
反馈
您对本页面有任何反馈吗？ 我们很乐意听取您的意见！