HQL update error

Mogaba · **Joined:** Mon Feb 11, 2013 2:15 am **Posts:** 3

Hello,

I have a problem with the following HQL query:

Code:

UPDATE ServiceStatus _ss
SET date = date + :dateOffset, status =
   (SELECT status FROM ServiceStatus
   WHERE date IN
      (SELECT MAX(date) FROM ServiceStatus)
   AND service = _ss.service)
WHERE date < :newMinDate

The generated sql:

Code:

update public.service_status set date=date+?, status=(select servicesta1_.status from public.service_status servicesta1_ where (servicesta1_.date in (select max(servicesta2_.date) from public.service_status servicesta2_)) and servicesta1_.service=servicesta0_.service) where date<?

I get an error:

Code:

Feb 11, 2013 9:58:22 AM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
WARN: SQL Error: 0, SQLState: 42P01
Feb 11, 2013 9:58:22 AM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
ERROR: ERROR: missing FROM-clause entry for table "servicesta0_"

Is it because there is no servicesta0_ alias for service_status in the update clause? How can I fix it?
I'm using Hibernate 4.1.9 with Postgres 9.1

Ajit Singh · **Joined:** Wed Feb 06, 2013 2:43 am **Posts:** 46

Can you please try this and see if it works :

UPDATE ServiceStatus _ss
SET date = date + :dateOffset, status =
(SELECT status FROM ServiceStatus s2
WHERE date IN
(SELECT MAX(date) FROM ServiceStatus)
AND s2.service = _ss.service)
WHERE date < :newMinDate

Please let me know the results.

Mogaba · **Joined:** Mon Feb 11, 2013 2:15 am **Posts:** 3

Hi, Ajit Singh

I already tried this and it didn't work either.

Ajit Singh · **Joined:** Wed Feb 06, 2013 2:43 am **Posts:** 46

I have tried with making similar case as yours . It works good for me.

But you might have some different scenario. i will let you know quickly as i find something.
This is an interesting problem by the way.

Mogaba · **Joined:** Mon Feb 11, 2013 2:15 am **Posts:** 3

Can you please post the generated sql for your query?