How to make LIMIT work in Grails HQL executeUpdate()?

I'm running grails 1.3.6 and I have this code:

String hql = '''
    UPDATE
        ApiResponse a
    SET
        a.lockId = :lockId
    WHERE
        a.lockId = 0
    ORDER BY
        a.dateAdded asc
    LIMIT 5
    '''
ApiResponse.executeUpdate(hql, [lockId : workerId])

It seems that this code updates all rows in DB instead of the 5 oldest entries. Does this mean LIMIT is not working in HQL? Please help me how to achieve the same SQL logic in GORM or HQL. Basically, I need to do开发者_Python百科 a bulk update using LIMIT.

---

what i do (grails 1.3.7):

ExAus.executeQuery( "select distinct <field> from <controller>", [max: 20, offset: 0] )

---

While waiting for someone to reply, I think I found a workaround. Here it is:

def c = ApiResponse.createCriteria()
def targetList = c.list {
    eq('lockId', 0)
    maxResults(5)
    order("dateAdded", 'asc')
}

String hql = '''
    UPDATE
        ApiResponse
    SET
        lockId = :lockId
    WHERE
        id in (:ids)
'''
ApiResponse.executeUpdate(hql, [lockId : workerId, ids: targetList.collect {it.id}])

I believe this approach can still be considered same logic with the original. However, this has to make 2 queries.

Feel free to suggest other approaches. Thanks!

---

I know the post is quite old but the question is still relevant since I had the same problem.

I fell back to using Groovy sql (jdbc) instead.

You will need to inject the dataSource object within your service/controller first by using:

def dataSource

Then you may do this in your method:

String sqlQuery = '''
UPDATE
    API_RESPONSE a
SET
    a.LOCK_ID = :lockId
WHERE
    a.LOCK_ID = 0
ORDER BY
    a.DATE_ADDED asc
LIMIT 5
'''
def sql = new Sql(dataSource)
sql.executeUpdate(sqlQuery, [lockId : workerId])

Note that you will need to use the database native table and column names in the sql query.