No, this would result in ALL queued message records being updated. This could
be 1 or maybe 60.000 records. I need to update/fetch only some of these
(i.e. 100), and then send these to a worker thread.
I can't first update all and then select 100 of these, because the next
select will then return most (or maybe even all) of the same records as the
last select, unless I do an ORDER BY and select with an offset. Besides, I
don't want to do this, because of the priority element. If the manager class
is busy working with 60.000 low priority records, no high priority records
created AFTER the update will be picked up until all the 60.000 updated
messages are handled. I need to be sure that high priority records are
always picked up fast, causing low priority records to wait in queue.
Thanks for reading.
//Marius
Dies wrote:
>
> 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-tp16582059p16585777.html
Sent from the java.net - glassfish persistence mailing list archive at Nabble.com.