Offset and fetch in a delete statement-CodePudding

I want to delete some specific rows based on two dynamic parameters, P1 and P2 (Numbers)

Using a simple Select statement:

SELECT * FROM TABLE WHERE (conditions) OFFSET P1 ROWS FETCH NEXT P2 ROWS ONLY;

I simply want to delete these rows and I can't find a correct way to do it. What I'm doing right now is somthing like, and it doesn't look good:

DELETE TABLE WHERE id in (SELECT id FROM TABLE WHERE (conditions) OFFSET P1 ROWS FETCH NEXT P2 ROWS ONLY);

CodePudding user response：

Use the primary key or ROWID to access the rows:

DELETE FROM mytable
WHERE rowid IN
(SELECT rowid FROM mytable WHERE (conditions) OFFSET p1 ROWS FETCH NEXT p2 ROWS ONLY);

When running the query repeatedly, you will end up with a table consisting of p1 rows. But as mentioned by sticky bit in the request comments: without an ORDER BY these rows will be arbitrary.