1

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?

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
Kate T
  • 11
  • 1
  • 2
    Answered my own question, first clauses are not allowed in subquery. Enhancement request!! Had to create a temp table instead. – Kate T Nov 03 '21 at 20:10

1 Answers1

0

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.

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
  • When did ROWID's start getting reused, unless the table is dropped and re-created? – Joe R. Nov 11 '21 at 20:36
  • Any time when a row is deleted and the space is reused. – Jonathan Leffler Nov 11 '21 at 20:45
  • Hmm, but rows are only logically deleted and their space is not reused unless you truncate (compress) the table, so the logically deleted row still has its original ROWID. – Joe R. Nov 11 '21 at 20:51
  • It's logically deleted until the transaction commits. After that, the ROWID (slot on the page) can be reused whenever the server finds it convenient — e.g. instead of allocating a new extent. – Jonathan Leffler Nov 11 '21 at 21:16
  • Understood, but if enough records are logically deleted, wouldn't new extents be created anyway since the deleted rows are still physically occupying space? I also cannot understand why the OP or anyone else would rely on using ROWID in any query since they're a moving target. – Joe R. Nov 13 '21 at 12:58