Basically i want to update a status flag on all rows in one table based on a join on another table...

for eg in SQL

update TABLE_1 as A inner join TABLE_2 as B on A.table2_id = B.id set A.status = 2 where B.status = 9

so my guess in HSQL was along the lines of..

update TABLE_1 tb1 left join fetch tb1.table2 set tb1.status = 2 where tb1.table2.status = 9

however i'm getting errors saying that 'set' was expected.

is it possible to update one table whilst joining on another in HSQL...?

p.s - seems you can't run update statements in SQLQuery's either so i am a little stuck at the moment?

See 11.5. Modifying persistent objects:
http://www.hibernate.org/hib_docs/v3/re ... -modifying

See 14.3. Bulk update/delete:
http://www.hibernate.org/hib_docs/v3/re ... tch-direct

See Chapter 17. Native SQL:
http://www.hibernate.org/hib_docs/v3/re ... /#querysql

Note that for some features you need to run a recent version of Hibernate (3.0.5 resp. 3.1).

Best regards
Sven