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 elevate an error as follows:
SQL Error [1093] [HY000]: You may’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 generally known as MemSQL)
Fortunately, jOOQ can simply rework such queries for you, everytime you’re making an attempt to UPDATE
or DELETE
a goal desk, with a predicate that will depend 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.