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!