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, SQLite, Snowflake, 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 )
ClickHouse
UPDATE BOOK SET TITLE = 'New Title' WHERE 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
)
ASE, Access
/* UNSUPPORTED */
(These are currently generated with jOOQ 3.20, see #10141), or translate your own on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!