CODEWITHSUNDEEP
oracle-databasejpaormjpql
Michael Tontchev
Michael Tontchev

Reputation: 1109

JPQL update query with Oracle throwing odd error. How can I fix it?

I'm having some problems running some JPQL queries. In my domain model I have a City class, which has a name and a reference to a Country object, which in turn also has a name. I am trying to run this query:

Query q = em.createQuery("UPDATE City city SET city.name = 'Americaville' WHERE city.country.name = 'America'");
q.executeUpdate();

However, I get this exception:

 java.sql.SQLSyntaxErrorException: ORA-00971: missing SET keyword

    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:879)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207)
    at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1044)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1329)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3584)
    at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3665)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1352)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:208)

This problem appears to only arise when I add that "country" in the where clause. That is, if I do "UPDATE City city SET city.name = 'Americaville' WHERE city.name = 'San Francisco'", everything runs just fine.

I figured JPQL might not allow deep property traversal, but looking at the JPQL specification (http://docs.oracle.com/cd/E12839_01/apirefs.1111/e13946/ejb3_langref.html#ejb3_langref_bulk_ops), I see

single_valued_association_path_expression ::= identification_variable.{single_valued_association_field.}*single_valued_association_field

Doesn't this allow me to use deep paths? It seems like a rule that allows me to write entity.property1.other.more.field .

Thanks!

Upvotes: 4

Views: 997

Answers (1)

Michael Tontchev
Michael Tontchev

Reputation: 1109

It turns out that JPQL doesn't allow joins in its update clauses. The workaround is to use subqueries in the where clause to select the proper rows to update.

Upvotes: 5

Related Questions