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

Question

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!

The query is valid IMHO, just that to achieve it the SQL generated would probably have to have multiple tables in the FROM clause (most RDBMS don't allow that in an UPDATE statement) OR the JPA implementation maybe could do something with subqueries? — Neil Stockton, Apr 20 '15 at 17:40
Gah, it does seem like it's RDBMS-dependent: http://stackoverflow.com/questions/1293330/how-can-i-do-an-update-statement-with-join-in-sql But shouldn't JPA/JPQL be able to rewrite the query to fit the SQL dialect in question? — Michael Tontchev, Apr 20 '15 at 17:55
what is the DDL for table "City"? (assuming City is a table name). How resulting SQL looks like? — ibre5041, Apr 20 '15 at 18:17

score 4 · Accepted Answer · answered May 04 '15 at 22:59

4

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.

answered May 04 '15 at 22:59

Michael Tontchev

909
8
23

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

1 Answers1