1

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.

codeCompiler77
  • 508
  • 7
  • 22

1 Answers1

1

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

  • Not what i was trying to achieve. I know of `LIMIT`, i could achieve the same thing with rownum. I am trying to get a loop going in HSQLDB so i don't have to run that one statement manually 100 or even 1000 times. – codeCompiler77 Aug 30 '16 at 15:28