Workaround for MySQL’s “can’t specify target table for update in FROM clause” Error

In MySQL, you cannot do this: The UPDATE statement will raise an error as follows: SQL Error [1093] [HY000]: You can’t specify target table ‘t’ for update in FROM clause People have considered this to be a bug in MySQL for ages, as most other RDBMS can do this without any issues, including MySQL clones: … Continue reading Workaround for MySQL’s “can’t specify target table for update in FROM clause” Error →

Workaround for MySQL’s “can’t specify target table for update in FROM clause” Error

Posted on December 20, 2023December 4, 2023 by lukaseder

In MySQL, you cannot do this:

create table t (i int primary key, j int);
insert into t values (1, 1);

update t
set j = (select max(j) from t) + 1;

The UPDATE statement will raise an error as follows:

SQL Error [1093] [HY000]: You can’t specify target table ‘t’ for update in FROM clause

People have considered this to be a bug in MySQL for ages, as most other RDBMS can do this without any issues, including MySQL clones:

• MariaDB 10.2

• SingleStore 6 (previously known as MemSQL)

Luckily, jOOQ can easily transform such queries for you, whenever you’re trying to UPDATE or DELETE a target table, with a predicate that depends on the target table itself. In those cases, jOOQ will just apply the following workaround:

update t
set j = (
select *
from (
select max(j) from t
) t
) + 1;

Now, the query works without any syntactic issues. Similar workarounds are documented in the MySQL docs, but with jOOQ, you simply don’t have to think about this limitation.

• Facebook
• LinkedIn
• X
• Reddit
• More

Like this:

Like Loading...

###
Published by lukaseder

I made jOOQ View all posts by lukaseder

Original source