openjpa-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Ognjen Blagojevic <ogn...@etf.bg.ac.yu>
Subject Re: Native DELETE on MySQL
Date Tue, 08 Apr 2008 08:02:09 GMT
Ignacio Andreu wrote:
> If you don't use an alias the bulk delete works ok, I tested it with the
> SupportsSubselect=true and I didn't have any problem. I used this code:
> 
> Query q = em.createQuery("delete from Test");
> q.executeUpdate();

I must use an alias since my query is more like:

   Query q = em.createQuery("delete from Test t where t.idSomething = 1");
   q.executeUpdate();

Which generates the following SQL:

DELETE
   FROM test
  WHERE id_test IN (SELECT DISTINCT t0.id_test
                      FROM Test t0
                     WHERE (t1.id_something = ?))

And that throws MySQL exception:

   "You can't specify target table 'test' for update in FROM clause".

Indeed, MySQL doesn't allow to use the same table in subselect and 
DELETE/UPDATE [1]:

"You can use a subquery for assignment within an UPDATE statement 
because subqueries are legal in UPDATE and DELETE statements as well as 
in SELECT statements. However, you cannot use the same table (in this 
case, table t1) for both the subquery's FROM clause and the update target."

So I believe that this is OpenJPA bug - already opened as OPENJPA-459 [2].


Regards,
Ognjen

[1] http://dev.mysql.com/doc/refman/5.0/en/subquery-errors.html
[2] https://issues.apache.org/jira/browse/OPENJPA-459

Mime
View raw message