This documentation is for the unreleased development version of jOOQ. Click on the above version links to get this documentation for a supported version of jOOQ.
UPDATE .. ORDER BY .. LIMIT
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
The ORDER BY and LIMIT clauses allow for updating only a subset of the data in a table, based on their ordering. This can be useful to implement queue semantics, e.g. to update only the top row, and possibly return it in one go.
Dialect support
This example using jOOQ:
update(BOOK).set(BOOK.TITLE, "New Title").orderBy(BOOK.ID.asc()).limit(1)
Translates to the following dialect specific expressions:
-- AURORA_MYSQL, DB2, MARIADB, MYSQL, TRINO
UPDATE BOOK
SET
BOOK.TITLE = 'New Title'
ORDER BY BOOK.ID ASC
LIMIT 1
-- AURORA_POSTGRES, DUCKDB, REDSHIFT, SNOWFLAKE, SQLITE, VERTICA, YUGABYTEDB
UPDATE BOOK
SET
TITLE = 'New Title'
WHERE BOOK.ID IN (
SELECT BOOK.ID
FROM BOOK
ORDER BY BOOK.ID ASC
LIMIT 1
)
-- BIGQUERY, EXASOL, HSQLDB
UPDATE BOOK
SET
BOOK.TITLE = 'New Title'
WHERE BOOK.ID IN (
SELECT BOOK.ID
FROM BOOK
ORDER BY BOOK.ID ASC
LIMIT 1
)
-- COCKROACHDB
UPDATE BOOK
SET
TITLE = 'New Title'
ORDER BY BOOK.ID ASC
LIMIT 1
-- DERBY, H2, ORACLE
UPDATE BOOK
SET
BOOK.TITLE = 'New Title'
WHERE BOOK.ID IN (
SELECT BOOK.ID
FROM BOOK
ORDER BY BOOK.ID ASC
FETCH NEXT 1 ROWS ONLY
)
-- FIREBIRD
UPDATE BOOK
SET
BOOK.TITLE = 'New Title'
ORDER BY BOOK.ID ASC
ROWS 1
-- HANA
UPDATE BOOK
FROM BOOK
SET
BOOK.TITLE = 'New Title'
WHERE BOOK.ID IN (
SELECT BOOK.ID
FROM BOOK
ORDER BY BOOK.ID ASC
LIMIT 1
)
-- INFORMIX
UPDATE BOOK
SET
BOOK.TITLE = 'New Title'
WHERE BOOK.ID IN (
SELECT *
FROM (
SELECT FIRST 1 BOOK.ID
FROM BOOK
ORDER BY BOOK.ID ASC
) x
)
-- MEMSQL
UPDATE BOOK
SET
BOOK.TITLE = 'New Title'
WHERE BOOK.ID IN (
SELECT *
FROM (
SELECT BOOK.ID
FROM BOOK
ORDER BY BOOK.ID ASC
LIMIT 1
) t
)
-- POSTGRES
UPDATE BOOK
SET
TITLE = 'New Title'
WHERE BOOK.ID IN (
SELECT BOOK.ID
FROM BOOK
ORDER BY BOOK.ID ASC
FETCH NEXT 1 ROWS ONLY
)
-- SQLDATAWAREHOUSE, SQLSERVER, SYBASE
UPDATE BOOK
SET
BOOK.TITLE = 'New Title'
WHERE BOOK.ID IN (
SELECT TOP 1 BOOK.ID
FROM BOOK
ORDER BY BOOK.ID ASC
)
-- TERADATA
UPDATE BOOK
SET
TITLE = 'New Title'
WHERE BOOK.ID IN (
SELECT *
FROM (
SELECT TOP 1 BOOK.ID
FROM BOOK
ORDER BY BOOK.ID ASC
) x
)
-- ACCESS, ASE
/* UNSUPPORTED */
(These are currently generated with jOOQ 3.19, see #10141), or translate your own on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!