The table is listed twice, both as a target for "UPDATE" and as a separate data source
I am using spring-jpa with hibernate implementation. I am using mariadb and I am trying to do an update subquery
The structure of my object
@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 )
I am getting this error
java.sql.SQLException: table "number" is specified twice, both as a target for "UPDATE" and as a separate data source
source to share
This is a limitation in MySQL: -
http://dev.mysql.com/doc/refman/5.7/en/update.html
You cannot update a table and select from the same table in a subquery.
Sometimes, you can infer which you can draw to hide an extra query in the next level of subquery 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
)
Please note that your request may have an error. In the subquery, you are joining the Room aliased table as r2 to a table called apartment in the database called r2. Also your subquery does a JOIN without a join condition.
However, you may well be able to 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
source to share