H2 Removal Request Limit

Question

I am trying to delete entries from the h2 database (version 1.0.71), but I am getting a query syntax error. Request in progress:

DELETE FROM TABLE_NAME WHERE QUERY_FIELD LIKE '%somevalue%' LIMIT 1000; 

The error message I receive (and which is not very useful to me):

 Syntax error in SQL statement DELETE FROM TABLE_NAME WHERE QUERY_FIELD LIKE '%somevalue%' LIMIT[*] 1000; 

The LIMIT part seems to be a problem, isn't it supported in h2 1.0.71?

When I execute a similar SELECT query:

 SELECT * FROM TABLE_NAME WHERE QUERY_FIELD LIKE '%somevalue%' LIMIT 1000; 

This gives me the expected results. Is a combination of LIKE and LIMIT problematic?

Answer 1

You can put a SELECT in a WHERE as follows:

 DELETE FROM TABLE_NAME WHERE QUERY_FIELD LIKE '%somevalue%' AND id_field IN (SELECT id_field FROM table_name WHERE QUERY_FIELD LIKE '%somevalue%' LIMIT 1000) 

Answer 2

This solution should work even with older versions of H2:

 DELETE FROM TABLE_NAME WHERE QUERY_FIELD LIKE '%somevalue%' AND ROWNUM() < 1000; 

Answer 3

Try

 DELETE TOP 1000 FROM TABLE_NAME WHERE QUERY_FIELD LIKE '%somevalue%'; 

Answer 4

Go to a later version of H2, and then use LIMIT , just like you.

The H2 version you are using (1.0.71) is about 5 years old and is no longer supported. To upgrade, create an SQL script (using the SCRIPT statement), and then run the script.