|
It is not possible to have a bulk insert and select with subselect with a field reference:
INSERT INTO table1 (column1,column2,...) SELECT (column1,column2,...) FROM table2 WHERE table2.column1 = (SELECT column1 from table3 WHERE table2.column1 = table3.column1)
The last where clause leads to wrong SQL that leads to SQL-Exception, because it references not the table alias!
Usually alle columns will be referenced with a table alias, but in the last where clause it references the table directly without alias which leads to unrecognized column.
INSERT INTO SecondPerson (id, name, vorname, date) SELECT p.id, p.name, p.vorname, p.date FROM Person p WHERE p.date = (select max(p2.date) FROM Person p2 WHERE p.id = p2.id)
generated SQL: Hibernate: insert into SecondPerson ( id, name, vorname, date ) select person0_.id as col_0_0_, person0_.name as col_1_0_, person0_.vorname as col_2_0_, person0_.date as col_3_0_ from Person person0_ where person0_.date=(select max(person1_.date) from Person person1_ where Person.id=person1_.id)
SQL-Exception: ERROR: Unknown column 'Person.id' in 'where clause'
It should be: ... where person0_.id=person1_.id)
Hibernate: insert into SecondPerson ( id, name, vorname, date ) select person0_.id as col_0_0_, person0_.name as col_1_0_, person0_.vorname as col_2_0_, person0_.date as col_3_0_ from Person person0_ where person0_.date=(select max(person1_.date) from Person person1_ where person0_.id=person1_.id)
|