JAVA

jOOQ workaround for “can’t specify target table for update in FROM clause”

0 4 1 minute read

In MySQL, you cannot do this:

create table t (i int primary key, j int);
insert into t values (1, 1);

update t
set j = (select max(j) from t) + 1;

The UPDATE statement will raise an error as follows:

SQL Error [1093] [HY000]: You can’t specify target table ‘t’ for update in FROM clause

People have considered this to be a bug in MySQL for ages, as most other RDBMS can do this without any issues, including MySQL clones:

MariaDB 10.2
SingleStore 6 (previously known as MemSQL)

Luckily, jOOQ can easily transform such queries for you, whenever you’re trying to UPDATE or DELETE a target table, with a predicate that depends on the target table itself. In those cases, jOOQ will just apply the following workaround:

update t
set j = (
  select *
  from (
    select max(j) from t
  ) t
) + 1;

Now, the query works without any syntactic issues. Similar workarounds are documented in the MySQL docs, but with jOOQ, you simply don’t have to think about this limitation.