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.
