如何进行删除查询?

Assume I have the following database table structure:

假设我有以下数据库表结构:

A >- B -< C >- D

>- B -< C >- D。

where >- is a many-to-one relation and -< one-to-many.

其中>-是一个多对一关系和- <一对多关系。< p>

Now I would like to delete all A entries which correspond to an D with a given name.

现在我想删除所有与给定名称的D对应的条目。

One might say that

有人可能会说,

DELETE FROM A JOIN B ON <condition> JOIN C ON <condition> JOIN D ON <condition> WHERE D.name=?

would be a solution. Sadly, it appears that you cannot have a JOIN clause in a DELETE query.

将是一个解决方案。遗憾的是，在DELETE查询中似乎不能使用JOIN子句。

Another approach was to have a sub-query, like this:

另一种方法是使用子查询，如下所示:

DELETE FROM A AS elem WHERE elem.id IN ( SELECT id FROM A JOIN B ON <condition> JOIN C ON <condition> JOIN D ON <condition> WHERE D.name=?);

While this might work in Oracle, MySQL will not let me do this(I cannot make a select on the table I'm about to delete from).

虽然这在Oracle中是可行的，但是MySQL不会允许我这样做(我不能在将要删除的表上进行选择)。

So how should I accomplish this?

那么我该怎么做呢?

To be more precise, I am using Hibernate and it's HQL to create this query. So a JPA/Hibernate solution would be preferred.

更准确地说，我正在使用Hibernate，创建这个查询的是HQL。所以最好使用JPA/Hibernate解决方案。

Of course, simple SQL will also do(I will try to translate it into HQL).

当然，简单的SQL也会这样做(我将尝试将其转换为HQL)。

1 个解决方案

#1

Yes, you can delete with a join. The syntax is described at the MySQL docs: DELETE

是的，您可以使用join来删除。在MySQL文档中描述了语法:DELETE。

DELETE A                     -- you only need to add `A` here, so it knows
                             -- which table to delete from
FROM A 
  JOIN B ON <condition> 
  JOIN C ON <condition> 
  JOIN D ON <condition> 
WHERE D.name = ? ;

The subquery approach will work, too. If the condition that is used to do the A join B is ON A.xid = B.xid, then you can use:

子查询方法也可以工作。如果用于执行A连接B的条件在A上。xid = B。xid，那么你可以使用:

DELETE FROM A 
WHERE A.xid IN 
      ( SELECT B.xid 
        FROM B 
          JOIN C ON <condition> 
          JOIN D ON <condition> 
        WHERE D.name = ?
      ) ;

but I would'n use this. Subqueries with IN sometimes do not perform well.

但我不会用这个。使用IN的子查询有时不能很好地执行。

Another approach is a correlated EXISTS subquery:

另一种方法是关联存在子查询:

DELETE FROM A 
WHERE EXISTS 
      ( SELECT 1  
        FROM B 
          JOIN C ON <condition> 
          JOIN D ON <condition> 
        WHERE D.name = ?
          AND A.xid = B.xid       -- the condition for "A JOIN B"
      ) ;

#1