Actually, I also wonder why you need the subquery.
Wouldn't the following result in the same records getting updated?
UPDATE MobileMessage x
SET x.statusmsg = 'PROCESSING',x.transporttime = :transporttime
WHERE
y.gateway = :gateway
AND y.statusmsg IN ( 'PENDING','RETRY','SCHEDULED')
AND ((y.timetosend IS NULL) OR (y.timetosend < :cutoff ))
Regards,
Dies
MariusW wrote:
>
> I have a nested query that throws this exception:
> Caused by: Exception [TOPLINK-8025] (Oracle TopLink Essentials - 2.0
> (Build b58-rc1 (08/04/2007))):
> oracle.toplink.essentials.exceptions.EJBQLException
> Exception Description: Syntax error parsing the query
> [updateProcessingCutoff: UPDATE MobileMessage x SET x.statusmsg =
> 'PROCESSING',x.transporttime = :transporttime WHERE x.id IN (SELECT y.id
> FROM MobileMessage y WHERE y.gateway = :gateway AND y.statusmsg IN (
> 'PENDING','RETRY','SCHEDULED') AND ((y.timetosend IS NULL) OR
> (y.timetosend < :cutoff )) ORDER BY y.priority)], line 1, column 273:
> unexpected token [ORDER].
> Internal Exception: line 1:273: unexpected token: ORDER
>
> In a more readable format, the EJB QL looks like this:
> UPDATE MobileMessage x
> SET x.statusmsg = 'PROCESSING',x.transporttime = :transporttime
> WHERE x.id IN (
> SELECT y.id FROM MobileMessage y
> WHERE y.gateway = :gateway
> AND y.statusmsg IN ( 'PENDING','RETRY','SCHEDULED')
> AND ((y.timetosend IS NULL) OR (y.timetosend < :cutoff ))
> ORDER BY y.priority
> )
>
> If I translate it to standard SQL and execute it, it works fine:
> UPDATE bb_core.bb_message
> SET statusmsg = 'PROCESSING'
> WHERE id IN (
> SELECT id FROM bb_core.bb_message
> WHERE gateway = 1
> AND statusmsg IN ( 'PENDING','RETRY','SCHEDULED')
> AND ((timetosend IS NULL) OR (timetosend < now() ))
> ORDER BY priority
> )
>
> Any tips would be greatly appreciated!
>
> // Marius
>
--
View this message in context: http://www.nabble.com/Unexpected-token%3A-ORDER-tp16582059p16585353.html
Sent from the java.net - glassfish persistence mailing list archive at Nabble.com.