Hibernate: update to sum from other table

Question

I'm trying to replicate this query from MySQL to HQL: *UPDATE users u, mines m SET u.mana = u.mana + COALESCE((SELECT SUM(m.mana_rate) FROM mines m WHERE m.user_id = u.id), 0)*

Simply doing this in HQL: update User u set u.mana = u.mana + (select coalesce(sum(m.manaRate), 0) from Mine m where m.userId = u.id)

Gives following error: Exception in thread "main" org.hibernate.hql.ast.QuerySyntaxException: unexpected AST node: query [update mm.user.User u set u.mana = u.mana + (select coalesce(sum(m.manaRate), 0) from mm.map.buildings.mines.Mine m where m.userId = u.id)]

I'm new to Hibernate, so I don't really know where to start, and what the error means. If it's not possible, can i write a native MySQL query in Hibernate?

EDIT: The problem seems to be in "u.mana = u.mana +", because when I just do u.mana = (select... the query works just fine

Answer 1

you can write native queries in hibernate, more about that here. bit sure what is wrong with your query though.