jOOQ workaround for “cannot specify goal desk for replace in FROM clause”

In MySQL, you can not do that:

create desk t (i int main key, j int);
insert into t values (1, 1);

replace t
set j = (choose max(j) from t) + 1;

The UPDATE assertion will increase an error as follows:

SQL Error [1093] [HY000]: You possibly can’t specify goal desk ‘t’ for replace in FROM clause

Individuals have thought of this to be a bug in MySQL for ages, as most different RDBMS can do that with none points, together with MySQL clones:

• MariaDB 10.2
• SingleStore 6 (beforehand often called MemSQL)

Fortunately, jOOQ can simply remodel such queries for you, everytime you’re making an attempt to UPDATE or DELETE a goal desk, with a predicate that relies on the goal desk itself. In these instances, jOOQ will simply apply the next workaround:

replace t
set j = (
  choose *
  from (
    choose max(j) from t
  ) t
) + 1;

Now, the question works with none syntactic points. Related workarounds are documented within the MySQL docs, however with jOOQ, you merely don’t have to consider this limitation.