JPA update with a join

Question

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?

score 3 · Answer 1 · edited May 23 '17 at 12:15

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

You may also encounter this exception when dealing with some DB specific functions which may not be known to the dialect you're using. That's just a sidenote. — EAT_YOUR_VEGGIES, Feb 17 '16 at 22:49

score 0 · Answer 2 · answered Feb 18 '16 at 22:21

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.

JPA update with a join

2 Answers2