使用连接或嵌套子查询Hibernate（HQL）进行更新

Question

我在创建使用嵌套查询或使用联接更新实体CommitteeMembership的HQL时遇到问题，我首先尝试了以下查询：

update CommitteeMemberShip cms set cms.isDeleted=1 
where cms.committeeCycle.committee.id=:committeeId

但生成的SQL错误如下：

update CommitteeMemberShip cross join  set isDeleted=1 where committee_id=?

在“交叉连接”之后没有任何this使Hibernate抛出SQLGrammarException

之后，我将查询更改为使用子查询：

update CommitteeMemberShip cms set cms.isDeleted=1 
where cms.id in (select cmsIn.id from CommitteeMemberShip cmsIn inner join 
cmsIn.committeeCycle cc inner join cc.committee c where c.id=:committeeId)

现在Hibernate投球

java.sql.SQLException: You can't specify target table 'CommitteeMemberShip' for 
update in FROM clause

任何人都知道我如何在HQL中编写此更新查询？？

Answer 1

HQL 似乎还不支持 DML 语句中的路径表达式/隐式联接。以下是 jOOQ 的操作方法（在 SQL 中，假设所有路径都是一对一关系）：

UPDATE CommitteeMemberShip cms
SET cms.isDeleted = 1
WHERE (
  SELECT Committee.id
  FROM Committee
  WHERE (
    SELECT CommitteeCycle.committeeId
    FROM CommitteeCycle
    WHERE CommitteeCycle.id = cms.committeeCycleId
  ) = Committee.id 
) = :committeeId

您自己的方法不起作用，因为您不必要地重复了< code>CommitteeMemberShip实体。这是一个已知的MySQL限制，参见:MySQL错误1093 -不能在FROM子句中指定更新的目标表

我怀疑这会起作用吗？

UPDATE CommitteeMemberShip cms
SET cms.isDeleted = 1
WHERE cms.committeeCycleId IN (
  SELECT CommitteeCycle.id
  FROM CommitteeCycle cc
  JOIN cc.committee c
  WHERE c.id = :committeeId
)

从Hibernate 6及其对派生表的支持开始，您可能能够像这样解决MySQL的限制(与jOOQ开箱即用的方式相同，参见此处):

UPDATE CommitteeMemberShip cms 
SET cms.isDeleted=1 
WHERE cms.id IN (
  -- Wrap the query once more in a derived table
  SELECT t.id
  FROM (
    SELECT cmsIn.id 
    FROM CommitteeMemberShip cmsIn 
    JOIN cmsIn.committeeCycle cc 
    JOIN cc.committee c WHERE c.id=:committeeId
  ) t
)

Answer 2

试试这个“UPDATE table 1r SET r . column = @ value WHERE r . column IN(" SELECT rd。Id来自r.table2 rd，其中rd。Column='" @value " '和rd。Column = ' " value

Answer 3

试试这个

更新Committee membership CMS set CMS . is deleted = 1，其中cms.id = some(从CommitteeMemberShip cmsIn内部联接cmsIn.committeeCycle cc内部联接cc.committee c中选择cmsIn.id，其中c.id=:committeeId)