Jooq Workaround for “Can’t Specific Target Table for Update in from Clause”

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 considesdered 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 Memsq)

Luckily, jooq can easily transform UPDATE or DELETE A target table, with a predicate that depends on the target table itself. In that 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 dated in the mysql docs, but with jooq, you simply don’t to have to think about this limitation.