Available in versions: Dev (3.22) | Latest (3.21) | 3.20 | 3.19 | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12
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 and DELETE without WHERE
Supported by ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
Forgetting the WHERE clause of an UPDATE or DELETE statement is one of the most costly mistakes in SQL, as the statement affects all rows of the table. jOOQ checks every UPDATE and DELETE statement for the presence of a WHERE clause prior to executing it, and takes an action according to the executeUpdateWithoutWhere and executeDeleteWithoutWhere settings, respectively. The possible values of org.jooq.conf.ExecuteWithoutWhere are:
-
IGNORE: The statement is executed without any further action. -
LOG_DEBUG(the default): The statement is executed, and aDEBUGlevel message is logged. -
LOG_INFO: The statement is executed, and anINFOlevel message is logged. -
LOG_WARN: The statement is executed, and aWARNlevel message is logged. -
THROW: The statement is not executed. Instead, aorg.jooq.exception.DataAccessExceptionis thrown.
The check happens only when the statement is executed by jOOQ. It has no effect on rendering the SQL string. A statement is considered to have a WHERE clause if any condition other than DSL.noCondition() was supplied, so in order to deliberately update or delete all rows of a table when THROW is active, use DSL.trueCondition():
// Throws a DataAccessException when executeUpdateWithoutWhere = THROW
create.update(BOOK)
.set(BOOK.TITLE, "Untitled")
.execute();
// Explicitly updates all rows
create.update(BOOK)
.set(BOOK.TITLE, "Untitled")
.where(trueCondition())
.execute();
Example configuration
Settings settings = new Settings()
.withExecuteUpdateWithoutWhere(ExecuteWithoutWhere.THROW) // Defaults to LOG_DEBUG
.withExecuteDeleteWithoutWhere(ExecuteWithoutWhere.THROW); // Defaults to LOG_DEBUG
Feedback
Do you have any feedback about this page? We'd love to hear it!