CODEWITHSUNDEEP
sqlhsqldbsquirrel-sql
codeCompiler77
codeCompiler77

Reputation: 516

Batch delete in HSQLDB and SQuirreL

I am trying to create an sql statement to delete records that match a certain condition by 1000, everything i try however doesn't work. For example i have tried

DECLARE @count int
SET @count = 1000
    DELETE  FROM HISTORY 
    WHERE HISTORYID IN (
        SELECT TOP (@count) HISTORYID
        FROM HISTORY
        WHERE HISTTYPE = 14
    )

but DECLARE does not work in HSQLDB. Please someone give me some advice on how this can be achieved via SQL, don't want to do it from the java side.

The reason is simple, i have heap error when it tries to delete 150k records, so i want to split the delete up into smaller pieces.

Upvotes: 1

Views: 501

Answers (1)

user330315
user330315

Reputation:

As documented in the manual HyperSQL supports the LIMIT statement for DELETE

So just run this until no rows are deleted any more:

DELETE FROM HISTORY
WHERE HISTTYPE = 14
LIMIT 1000;

Don't forget to commit or run in auto-commit mode

Upvotes: 1

Related Questions