MS SQL Server UPDATE statement with JOIN in Spring and Hibernate

Question

I have two classes:

Course {
  ...
  private List<Student> students;
  private String var1;
  ...
}

Student {
  private Long id;
  ...
}

In my code, I was able to generate the following query dynamically and get results successfully:

SELECT c.var1 FROM Course c, IN (c.students) s WHERE s.id = :sid

Now I would like to do a update with the following dynamically generated query:

UPDATE c SET c.var1 = 'newvalue' FROM Course c, IN (c.students) s WHERE s.id = :sid

For this, I always got error:

org.springframework.dao.InvalidDataAccessApiUsageException: node to traverse cannot be null!; nested exception is java.lang.IllegalArgumentException: node to traverse cannot be null!

The reason I came up with the above UPDATE query is because I read this SO post:

How can I do an UPDATE statement with JOIN in SQL?

Here is my code:

Query query = em.createQuery("UPDATE c SET c.var1 = 'newvalue' FROM Course c, IN (c.students) s WHERE s.id = :sid");
query.setParameter("sid", 10);
query.executeUpdate();

Thanks for help!

you cannot add FROM clauses in JPQL UPDATE statements, as any docs would show you. http://www.datanucleus.org/products/accessplatform_4_2/jpa/jpql.html#JPQL_UPDATE_Queries — Neil Stockton, Feb 09 '16 at 16:27
Neil, thanks sooooo much for chiming in. Based on your input, I found another post at SO that solved my problem. Basically, I need to use a subquery. http://stackoverflow.com/questions/6201895/update-value-with-join — curious1, Feb 09 '16 at 21:47

score 1 · Answer 1 · edited May 23 '17 at 11:52

Based on the info from Nei Stockton, I did further search and found another post at SO that solved my problem.

Update value with join

Basically, I need to use a subquery similar to the following given in the above post:

UPDATE Team t SET t.current = :current WHERE t.id in 
(select t1.id from Team t1  LEFT JOIN t1.members m WHERE t1.current = :current_true AND m.account = :account)

Cheers!

MS SQL Server UPDATE statement with JOIN in Spring and Hibernate

1 Answers1