Wednesday, December 20, 2023
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 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.

RELATED ARTICLES

LEAVE A REPLY

Please enter your comment!
Please enter your name here

- Advertisment -
Google search engine

Most Popular

Recent Comments