I have a question regarding how to use HQL to do a mass update of a database. I am using version 3.1.3 of Hibernate and trying to update a SQLServer 2005 database.

I want to do a bulk update of one table with the contents of another. I have tested the SQL statements and they are valid SQL Statements.

---------------------------------------------------------

My first attempt at this was as follows:

String sqlUpdate = "UPDATE TblAPDetails SET APPaymentId = b.APPaymentId from TblAPDetails AS a, TblAPPayments AS b WHERE a.CustomerOrDealer = b.CustomerOrDealer AND a.CustomerId = b.CustomerId";
rowsUpdatedCount = session.createQuery(sqlUpdate).executeUpdate();

Upon execution, I get the following error message:

2006-09-11 17:21:36,557 ERROR [main] dao.ChkFilDAO (ChkFilDAO.java:328) - Exception occured while updating ChkFilDAO
org.hibernate.hql.ast.QuerySyntaxError: unexpected token: from near line 1, column 53 [UPDATE TblAPDetails SET APPaymentId = b.APPaymentId from TblAPDetails AS a, TblAPPayments AS b WHERE a.CustomerOrDealer = b.CustomerOrDealer AND a.CustomerId = b.CustomerId]
at org.hibernate.hql.ast.ErrorCounter.throwQueryException(ErrorCounter.java:63)
at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:215)
at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:127)
at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:83)
at org.hibernate.impl.SessionFactoryImpl.getQuery(SessionFactoryImpl.java:427)
at org.hibernate.impl.SessionImpl.getQueries(SessionImpl.java:884)
at org.hibernate.impl.SessionImpl.executeUpdate(SessionImpl.java:865)
at org.hibernate.impl.QueryImpl.executeUpdate(QueryImpl.java:89)
at com.rgbk.ibs.arms.dao.ChkFilDAO.updateAPDetailAPPaymentId(ChkFilDAO.java:320)
at com.rgbk.ibs.arms.dao.ChkFilDAO.postConversionUpdates(ChkFilDAO.java:115)
at com.rgbk.ibs.arms.dao.AbstractDAO.processConversion(AbstractDAO.java:115)
at com.rgbk.ibs.arms.dao.AbstractDAO.processConversion(AbstractDAO.java:60)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at com.rgbk.ibs.arms.client.AbstractConversion.runConversion(AbstractConversion.java:50)
at com.rgbk.ibs.arms.unittest.conversion.TestConversion.main(TestConversion.java:12)
Caused by: line 1:53: unexpected token: from
at org.hibernate.hql.antlr.HqlBaseParser.updateStatement(HqlBaseParser.java:199)
at org.hibernate.hql.antlr.HqlBaseParser.statement(HqlBaseParser.java:133)
at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:209)
... 16 more
2006-09-11 17:21:36,597 ERROR [main] client.AbstractConversion (AbstractConversion.java:69) - Invocation target exception for method process on the object com.rgbk.ibs.arms.dao.ChkFilDAO
java.lang.reflect.InvocationTargetException
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at com.rgbk.ibs.arms.client.AbstractConversion.runConversion(AbstractConversion.java:50)
at com.rgbk.ibs.arms.unittest.conversion.TestConversion.main(TestConversion.java:12)
Caused by: org.hibernate.hql.ast.QuerySyntaxError: unexpected token: from near line 1, column 53 [UPDATE TblAPDetails SET APPaymentId = b.APPaymentId from TblAPDetails AS a, TblAPPayments AS b WHERE a.CustomerOrDealer = b.CustomerOrDealer AND a.CustomerId = b.CustomerId]
at org.hibernate.hql.ast.ErrorCounter.throwQueryException(ErrorCounter.java:63)
at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:215)
at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:127)
at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:83)
at org.hibernate.impl.SessionFactoryImpl.getQuery(SessionFactoryImpl.java:427)
at org.hibernate.impl.SessionImpl.getQueries(SessionImpl.java:884)
at org.hibernate.impl.SessionImpl.executeUpdate(SessionImpl.java:865)
at org.hibernate.impl.QueryImpl.executeUpdate(QueryImpl.java:89)
at com.rgbk.ibs.arms.dao.ChkFilDAO.updateAPDetailAPPaymentId(ChkFilDAO.java:320)
at com.rgbk.ibs.arms.dao.ChkFilDAO.postConversionUpdates(ChkFilDAO.java:115)
at com.rgbk.ibs.arms.dao.AbstractDAO.processConversion(AbstractDAO.java:115)
at com.rgbk.ibs.arms.dao.AbstractDAO.processConversion(AbstractDAO.java:60)
... 6 more
Caused by: line 1:53: unexpected token: from
at org.hibernate.hql.antlr.HqlBaseParser.updateStatement(HqlBaseParser.java:199)
at org.hibernate.hql.antlr.HqlBaseParser.statement(HqlBaseParser.java:133)
at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:209)
... 16 more

------------------------------------------------

When that didn't work, I tried to rewrite the SQL Query as follows:

String hqlUpdate = "UPDATE TblApdetails SET AppaymentId = (select AppaymentId from TblAppayments where tblApdetails.CustomerOrDealer = tblAPPayments.CustomerOrDealer AND tblApdetails.CustomerId = tblAPPayments.CustomerId)";
rowsUpdatedCount = session.createQuery(hqlUpdate).executeUpdate();

But then I received the following error message:

2006-09-11 17:24:07,854 ERROR [main] dao.ChkFilDAO (ChkFilDAO.java:328) - Exception occured while updating ChkFilDAO
org.hibernate.hql.ast.QuerySyntaxError: unexpected AST node: query [UPDATE TblApdetails SET AppaymentId = (select AppaymentId from com.rgbk.ibs.arms.model.TblAppayments where tblApdetails.CustomerOrDealer = tblAPPayments.CustomerOrDealer AND tblApdetails.CustomerId = tblAPPayments.CustomerId)]
at org.hibernate.hql.ast.ErrorCounter.throwQueryException(ErrorCounter.java:63)
at org.hibernate.hql.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:196)
at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:130)
at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:83)
at org.hibernate.impl.SessionFactoryImpl.getQuery(SessionFactoryImpl.java:427)
at org.hibernate.impl.SessionImpl.getQueries(SessionImpl.java:884)
at org.hibernate.impl.SessionImpl.executeUpdate(SessionImpl.java:865)
at org.hibernate.impl.QueryImpl.executeUpdate(QueryImpl.java:89)
at com.rgbk.ibs.arms.dao.ChkFilDAO.updateAPDetailAPPaymentId(ChkFilDAO.java:320)
at com.rgbk.ibs.arms.dao.ChkFilDAO.postConversionUpdates(ChkFilDAO.java:115)
at com.rgbk.ibs.arms.dao.AbstractDAO.processConversion(AbstractDAO.java:115)
at com.rgbk.ibs.arms.dao.AbstractDAO.processConversion(AbstractDAO.java:60)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at com.rgbk.ibs.arms.client.AbstractConversion.runConversion(AbstractConversion.java:50)
at com.rgbk.ibs.arms.unittest.conversion.TestConversion.main(TestConversion.java:12)
Caused by: <AST>:0:0: unexpected AST node: query
at org.hibernate.hql.antlr.HqlSqlBaseWalker.expr(HqlSqlBaseWalker.java:958)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.newValue(HqlSqlBaseWalker.java:701)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.assignment(HqlSqlBaseWalker.java:650)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.setClause(HqlSqlBaseWalker.java:553)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.updateStatement(HqlSqlBaseWalker.java:237)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:160)
at org.hibernate.hql.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:189)
... 16 more
2006-09-11 17:24:07,874 ERROR [main] client.AbstractConversion (AbstractConversion.java:69) - Invocation target exception for method process on the object com.rgbk.ibs.arms.dao.ChkFilDAO
java.lang.reflect.InvocationTargetException
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at com.rgbk.ibs.arms.client.AbstractConversion.runConversion(AbstractConversion.java:50)
at com.rgbk.ibs.arms.unittest.conversion.TestConversion.main(TestConversion.java:12)
Caused by: org.hibernate.hql.ast.QuerySyntaxError: unexpected AST node: query [UPDATE TblApdetails SET AppaymentId = (select AppaymentId from com.rgbk.ibs.arms.model.TblAppayments where tblApdetails.CustomerOrDealer = tblAPPayments.CustomerOrDealer AND tblApdetails.CustomerId = tblAPPayments.CustomerId)]
at org.hibernate.hql.ast.ErrorCounter.throwQueryException(ErrorCounter.java:63)
at org.hibernate.hql.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:196)
at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:130)
at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:83)
at org.hibernate.impl.SessionFactoryImpl.getQuery(SessionFactoryImpl.java:427)
at org.hibernate.impl.SessionImpl.getQueries(SessionImpl.java:884)
at org.hibernate.impl.SessionImpl.executeUpdate(SessionImpl.java:865)
at org.hibernate.impl.QueryImpl.executeUpdate(QueryImpl.java:89)
at com.rgbk.ibs.arms.dao.ChkFilDAO.updateAPDetailAPPaymentId(ChkFilDAO.java:320)
at com.rgbk.ibs.arms.dao.ChkFilDAO.postConversionUpdates(ChkFilDAO.java:115)
at com.rgbk.ibs.arms.dao.AbstractDAO.processConversion(AbstractDAO.java:115)
at com.rgbk.ibs.arms.dao.AbstractDAO.processConversion(AbstractDAO.java:60)
... 6 more
Caused by: <AST>:0:0: unexpected AST node: query
at org.hibernate.hql.antlr.HqlSqlBaseWalker.expr(HqlSqlBaseWalker.java:958)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.newValue(HqlSqlBaseWalker.java:701)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.assignment(HqlSqlBaseWalker.java:650)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.setClause(HqlSqlBaseWalker.java:553)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.updateStatement(HqlSqlBaseWalker.java:237)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:160)
at org.hibernate.hql.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:189)
... 16 more


-------------------------------------------

I have read the documentation, have did Google searches, searched the forums, but cannot discern any way to do this. My possible solution is just to use normal JDBC to update the database after it has been updated with Hibernate. But my concern then is that the database may not be totally updated by Hibernate by the time I do my separate update.

Any suggestions would be appreciated.

read up about bulk updates and you can use HQL to do it with.

SQL bulk updates is something you do via the raw connection and it doesn't matter for hibernate since even though you did it via createSQLQuery().executeUpdate() which is possible in 3.2 hibernate has no way of knowing what data you have changed precisly...same goes for the HQL update btw. with HQL we do though know which class has changed. but that is it.

Sorry. I made a typo. I didn't mean Bulk update, I meant a mass update. It has nothing to do with Bulk updates.

I see much in HQL about updating one record or even in updating mutiple records but nothing about updating a field in one table with the contents of another table.

I want to update a field in all the records of table A with a field in table B when certain criteria is present in both tables.

Basically my statement would be:
Update tableA set tableAField = (select tableBField from TableB where TableACritiera = TableBCriteria).

I have looked at the documentation on-line and through the book Hibernate in Action. I have tried using named queries, and rewriting the update statement so that it is a more of a join and nothing seems to work.

Note though that I used createQuery.executeUpdate(). I tried using createSQLQuery.executeUpdate() but when I did, I got an error message that said that I had to use HQL update.

If I am overlooking the relevant documentation (and I probably am), if someone would guide me to the appropriate links, that would help as well.

Or are you saying that I need to use Hibernate 3.2? That Hibernate 3.1 doesn't support it but 3.2 does?



Thanks.

bulk updates == mass updates ...no difference.


HQL has powerfull but in some case limited update functionallity. Use it if you can.

using createSQLQuery for bulk updates is only possible in Hibernate 3.2 but it is more or less *equal* to using the raw connection to execute it.