Delete from table on same select same table mariadb using jpa

Question

I need delete from table on operation of same table .JPA query is

DELETE  FROM com.model.ElectricityLedgerEntity a 
Where a.elLedgerid IN 
 (SELECT P.elLedgerid FROM
   (SELECT MAX(b.elLedgerid) 
    FROM com.model.ElectricityLedgerEntity b
    WHERE b.accountId='24' and b.ledgerType='Electricity Ledger' and b.postType='ARREARS') P );

I got this error:

with root cause org.hibernate.hql.ast.QuerySyntaxException: unexpected token: ( near line 1, column 109 [DELETE FROM com.bcits.bfm.model.ElectricityLedgerEntity a Where a.elLedgerid IN ( SELECT P.elLedgerid FROM ( SELECT MAX(b.elLedgerid) FROM com.bcits.ElectricityLedgerEntity b WHERE b.accountId='24' and b.ledgerType='Electricity Ledger' and b.postType='ARREARS') P ) ] at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:54) at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:47) at org.hibernate.hql.ast.ErrorCounter.throwQueryException(ErrorCounter.java:82) at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:284)

Same query is running on mysql terminal ,but this is not working with jpa .Can any one tell me how i can write this query using jpa .

Answer 1

You cannot do this in a single query with Hibernate. If you want to delete the max row(s) with Hibernate you will have to do so in two steps. First, you can find the max entry, then you can delete using that value in the WHERE clause.

But the query you wrote should actually run as a raw MySQL query. So why don't you try executing that query as a raw query:

String sql = "DELETE FROM com.model.ElectricityLedgerEntity a " +
             "WHERE a.elLedgerid IN (SELECT P.elLedgerid FROM " +
             "(SELECT MAX(b.elLedgerid) FROM com.model.ElectricityLedgerEntity b " + 
             "WHERE b.accountId = :account_id AND b.ledgerType = :ledger_type AND " +
             " b.postType = :post_type) P );";
Query query = session.createSQLQuery(sql);
query.setParameter("account_id", "24");
query.setParameter("ledger_type", "Electricity Ledger");
query.setParameter("post_type", "ARREARS");

Answer 2

I don't understand why do you use Pbefore the last parenthesis...

The following code is not enough ?

DELETE  FROM com.model.ElectricityLedgerEntity a 
Where a.elLedgerid IN    
  (SELECT MAX(b.elLedgerid) 
   FROM com.model.ElectricityLedgerEntity b
   WHERE b.accountId='24' and b.ledgerType='Electricity Ledger' and
   b.postType='ARREARS')    

Edit for bypassing mysql subquery limitations :

The new error java.sql.SQLException: You can't specify target table 'LEDGER' for update in FROM clause is known in mysql when you use it with JPA. It's one MySQL limitation. A recent stackoverflow question about it
In brief, you cannot "directly" updated/deleted a table that you query in a select clause

Now I understand why your original query did multiple subqueries seemingly not necessary (while it was useful for mysql) and had a "special" syntax. I don't know tricks to solve this problem in JPA (I don't use the MySQL DBMS for a long time now).

At your place, I would do two queries. The first where you select the expected max elLedgerid and the second where you could delete line(s) with the id retrieved in the previous query. You should not have performance issues if your sql model is well designed, the sql indexes well placed and the time to access to the database is correct.

Answer 3

Just want to extend existing answer:

In brief, you cannot "directly" updated/deleted a table that you query in a select clause

This was lifted with starting from MariaDB 10.3.1:

Same Source and Target Table

Until MariaDB 10.3.1, deleting from a table with the same source and target was not possible. From MariaDB 10.3.1, this is now possible. For example:

DELETE FROM t1 WHERE c1 IN (SELECT b.c1 FROM t1 b WHERE b.c2=0);