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

In MySQL, you can’t do that:

create desk t (i int major 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’ll be able to’t specify goal desk ‘t’ for replace in FROM clause

Folks 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 referred to as MemSQL)

Fortunately, jOOQ can simply rework such queries for you, everytime you’re attempting to UPDATE or DELETE a goal desk, with a predicate that will depend on the goal desk itself. In these circumstances, 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.