Search code examples
mysqldatabasehibernatehql

Hibernate Hql for bulk update using two entity tables

I am using this query in mysql for updating multiple values for the column2 in Table1 with the values in the column2 in Table2 on certain condition. but i am not able to give two entity names in the hibernate update hql. can i know how i have to write the query below in hql so that it supports all database.

UPDATE Table1 A, Table2 B SET A.column2 = B.column2 WHERE A.column1 = B.column1

Thanks, Saif

Solution

  • From Hibernate 4.1.9 manual - section about bulk update and deletes:

    No implicit or explicit joins can be specified in a bulk HQL query. Sub-queries can be used in the where-clause, where the subqueries themselves may contain joins.

    Unsupported (implicit join):

    DELETE A a WHERE a.relation.property = "dummy";

    A way to go:

    DELETE A a WHERE a.relation.id IN (SELECT r.id FROM Relation r WHERE r.property = "dummy");

    For your example it is bit complicated but following query should work (tested in HSQL db):

    UPDATE A a SET a.column3 = (SELECT b.column3 FROM B b WHERE a.column1 = b.column1 and a.column2 = b.column2) WHERE a.id IN (SELECT a.id FROM A a, B b WHERE a.column1 = b.column1 AND a.column2 = b.column2)

    This query works in HSQL db, but fails in MySQL. It looks like that only possible solution is to use two separate queries:

    List<String> list = em.createQuery("SELECT a.column1 FROM A a, B b WHERE a.column1 = b.column1 AND a.column2 = b.column2").getResultList();
em.createQuery("UPDATE A a SET a.column3 = (SELECT b.column3 FROM B b WHERE a.column1 = b.column1 and a.column2 = b.column2) WHERE a.column1 IN :list").setParameter("list", list).executeUpdate();

    The last solution is tested in mysql and works well but in your case you have to customize first select query based on your use case - my example expects that a.column1 is unique (using column1 instead of id to avoid primary key).