Wednesday, July 24, 2024
HomeProgrammingjOOQ workaround for "cannot specify goal desk for replace in FROM clause"

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.



Please enter your comment!
Please enter your name here

Most Popular

Recent Comments