How to limit the number of rows deleted in a query using rowid and first in Informix

Question

I can do this statement to get a rowid list:

select first 1000 rowid from table1 where last_update < today-30;

but I can't use it as a subquery for a delete statement:

delete from table1
 where rowid in ( select first 1000 rowid from table1
                   where last_update < today-30 );

It gives me this error:

944: Cannot use "first", "limit" or "skip" in this context.

How can I fix that?

Answer 1

As noted in your comment, the FIRST, LIMIT and SKIP clauses are not allowed in sub-queries. This is an annoying lack of consistency.

The workaround you allude to is:

DROP TABLE IF EXISTS temp_rowid_list;
SELECT FIRST 1000 ROWID
  FROM table1
 WHERE last_update < today - 30
  INTO TEMP temp_rowid_list;
DELETE FROM table1 WHERE ROWID IN (SELECT * FROM temp_rowid_list);
DROP TABLE IF EXISTS temp_rowid_list;

Unless you run with REPEATABLE READ isolation, there is a TOCTOU (time of check, time of use) risk in that some of the rows in the main table identified by ROWID values in the temporary table may have been altered between the time of the SELECT and the DELETE operations. There's a small chance that an old row may have been deleted and a new row inserted with the same ROWID.