JPA update with a join

I use spring-jpa with hibernate implementation. I use mariadb I try to do an update with a join.

My object structure

@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;
}

My sql query

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 

tried also

update  Room r1 set r1.available = :availability
    where  exists 
      ( SELECT  r2
            from  Room r2
            JOIN  r2.appartment a1
            where  a1.appartmentId= :appartmentId 
      ) 

I get this error

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

Seem like update and join with mysql seem impossible?

---

Everytime you get the unexpected token exception, look for syntax errors.

In your case the update query should look like this:

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 )  

EDIT (follow-up issue):

Here you may find some help for your follow-up question

The accepted answer features a MySQL example on how to perform an update with a JOIN. I'm quite sure that's what you're after. Now it would look something like this:

UPDATE Room r1 JOIN r1.appartment a1
SET r1.available = :availability
WHERE a1.appartmentId = :appartmentId

try it with your dialect but if it doesn't work, the following one should do the trick:

UPDATE Room r1 SET r1.available = :availability WHERE r1.appartment.appartmentId = :appartmentId

---

IN ( SELECT ... ) has poor performance characteristics. Since you are looking at an UPDATE , I recommend a multi-table UPDATE . Or EXISTS is good -- but not that EXISTS . You have not tied the two instances of Room together; the UPDATE will change all or none of the rows!

Since you have not explained what the query is supposed to do, I cannot advise you on how to do it.

链接地址: http://www.djcxy.com/p/94682.html

上一篇: 在SQL Server中分页结果的最佳方式是什么？

下一篇: JPA更新加入