CODEWITHSUNDEEP
sqlh2
Bertjan Broeksema
Bertjan Broeksema

Reputation: 1551

H2 delete query limit

I'm trying to delete records from a h2 database (version 1.0.71) but I get a query syntax error. The query I perform is:

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

The error message I get is (and which isn't very helpful to me):

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

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

When I perform the similar SELECT query:

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

It gives me the expected results. Is the combination of LIKE and LIMIT perhaps problematic?

Upvotes: 5

Views: 6420

Answers (4)

Thomas Mueller
Thomas Mueller

Reputation: 50097

This solution should work even with older versions of H2:

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

Upvotes: 2

Thomas Mueller
Thomas Mueller

Reputation: 50097

Upgrade to a more recent version of H2, and then use LIMIT like you did.

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

Upvotes: 1

Rachcha
Rachcha

Reputation: 8816

You can put a SELECT statement in the WHERE clause like this:

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)

Upvotes: 2

aaroncatlin
aaroncatlin

Reputation: 3271

Try

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

Upvotes: 0

Related Questions