假设我具有以下数据库表结构:
A>-B-< &
其中>-是多对一关系,而-
现在,我想删除所有与给定名称的D对应的A条目.
有人可能会说
DELETE FROM A JOIN B ON JOIN C ON JOIN D ON WHERE D.name=?
将是一个解决方案.可悲的是,似乎在DELETE查询中不能有JOIN子句.
另一种方法是进行子查询,如下所示:
DELETE FROM A AS elem WHERE elem.id IN ( SELECT id FROM A JOIN B ON JOIN C ON JOIN D ON WHERE D.name=?);
尽管这可能在Oracle中有效,但MySQL不允许我这样做(我无法在要删除的表上进行选择).
那我该怎么做呢?
更准确地说,我使用的是Hibernate,它是HQL来创建此查询.因此,将首选JPA / Hibernate解决方案.
当然,简单的SQL也可以(我会尝试将其转换为HQL).
解决方法:
是的,您可以通过联接删除.语法在MySQL文档中说明:DELETE
DELETE A -- you only need to add `A` here, so it knows
-- which table to delete from
FROM A
JOIN B ON
JOIN C ON
JOIN D ON
WHERE D.name = ? ;
子查询方法也将起作用.如果用于执行A连接B的条件为ON A.xid = B.xid,则可以使用:
DELETE FROM A
WHERE A.xid IN
( SELECT B.xid
FROM B
JOIN C ON
JOIN D ON
WHERE D.name = ?
) ;
但是我不会用这个带有IN的子查询有时效果不佳.
另一种方法是相关的EXISTS子查询:
DELETE FROM A
WHERE EXISTS
( SELECT 1
FROM B
JOIN C ON
JOIN D ON
WHERE D.name = ?
AND A.xid = B.xid -- the condition for "A JOIN B"
) ;
标签:hibernate,sql,mysql
来源: https://codeday.me/bug/20191122/2062638.html