HQL - Table is specified twice, both as a target for 'DELETE' and as a separate source for data

Question

I'm using Hibernate 4.3

I have OneToMany Commande --> Article

I want to delete Commande related to one Article. first of all i try to select the desired list of Commande.

        Query querydeleteCmd = em.createQuery("select c from Commande c where (:art) in elements(c.articles)");
        querydeleteCmd.setParameter("art", new Article(1));
        List<Commande> commandes = querydeleteCmd.getResultList();
        commandes.forEach(System.out::println);

This works fine, but when I want to delete (it's almost the same query) :

        Query querydeleteCmd = em.createQuery("delete from Commande c where (:art) in elements(c.articles)");
        querydeleteCmd.setParameter("art", new Article(1));
        querydeleteCmd.executeUpdate();

This gives me :

Caused by: java.sql.SQLException: Table 'Commande_Article' is specified twice, both as a target for 'DELETE' and as a separate source for data

Answer 1

The Exception says:

Table 'Commande_Article' is specified twice, both as a target for 'DELETE' and as a separate source for data

And it's due to the fact that you are using c.articles as a source/collection of data for in clause of where, this is not possible because here c is in the same time the target of the DELETE operation and the source of data for in clause, which is confusing in the query.

As you already did you need to pass the collection of articles as a parameter without referencing c in the where clause to avoid this problem.