表被指定了两次，既作为“更新"的目标，又作为数据的单独源

2022-01-15 00:00:00 mariadb mysql java Hibernate jpa

我使用带有休眠实现的 spring-jpa.我使用 mariadb 并尝试执行更新子查询

I use spring-jpa with hibernate implementation. I use mariadb and I try to do an update subquery

我的对象结构

@Entity
public class Room {
  @Id
  @GeneratedValue(strategy = GenerationType.AUTO)
  private Long roomId;

  @ManyToOne  
  @JoinColumn(name = "appartment_id")
  private Appartment appartment;
}

@Entity
public class Appartment {

  @Id
  @GeneratedValue(strategy = GenerationType.AUTO)
  private Long appartmentId;

  @OneToMany
  @JoinColumn(name="appartment_id")
  private Set<Room> roomList;
}

update Room r1
set r1.available = :availability
where r1.roomId in
( select r2.roomId from Room r2 JOIN r2.appartment a1 WHERE a1.appartmentId = :appartmentId )

我收到此错误

java.sql.SQLException: 表房间"被指定了两次，既作为更新"的目标，又作为数据的单独源

java.sql.SQLException: Table 'room' is specified twice, both as a target for 'UPDATE' and as a separate source for data

推荐答案

这是 MySQL 中的一个限制:-

This is a restriction in MySQL:-

http://dev.mysql.com/doc/refman/5.7/en/update.html

您不能在子查询中更新表并从同一个表中进行选择.

你可以做一个软糖有时将子查询隐藏在可能工作的更深层次的子查询中.像这样的东西(未测试):-

There is a fudge you can do sometimes do to hide the sub query in a a further level of sub query that might work. Something like this (not tested):-

UPDATE Room r1
SET r1.available = :availability
WHERE r1.roomId IN
    SELECT roomId
    FROM
    ( 
        SELECT r2.roomId 
        FROM Room r2 
        JOIN r2.appartment a1 
        WHERE a1.appartmentId = :appartmentId 
    )

请注意，您的查询可能有错误.在子查询中，您将别名为 r2 的表 Room 加入到名为 r2 的数据库上的名为 appartment 的表中.此外，您的子查询在没有连接条件的情况下执行 JOIN.

Note that your query possibly has an error. In the sub query you are joining the table Room aliased as r2 to a table called appartment on a database called r2. Also your sub query does a JOIN without a join condition.

但是，您很可能只在 UPDATE 语句中进行连接，而不需要子查询:-

However you can quite possibly just do the join in the UPDATE statement without the need for a sub query:-

UPDATE Room 
INNER JOIN r2.appartment a1
ON Room.roomId = a1.roomId
SET r1.available = :availability
WHERE a1.appartmentId = :appartmentId

相关文章