I'm trying to delete any inactive rows after a limit of 1000 rows, and I've tried this: but it's deleting everything from the table. EDIT: I've tried this, but it's deleting all inactive orders: I want to keep the first 1000 inactive orders and delete the remaining older inactive orders. I would also like to keep all active orders. Answer

Android Room database – Delete rows after row limit?

I’m trying to delete any inactive rows after a limit of 1000 rows, and I’ve tried this:

@Query("DELETE FROM limit_orders where time_created NOT IN (SELECT is_active FROM limit_orders WHERE is_active = 0 ORDER BY time_created DESC LIMIT 1000)")
void cleanInactiveLimitHistory();

but it’s deleting everything from the table.

EDIT:

I’ve tried this, but it’s deleting all inactive orders:

@Query("DELETE FROM limit_orders WHERE is_active = 0 NOT IN (SELECT is_active FROM limit_orders WHERE is_active = 0 ORDER BY time_created DESC LIMIT 1000)")

I want to keep the first 1000 inactive orders and delete the remaining older inactive orders. I would also like to keep all active orders.

Answer

in short you are comparing oranges to apples and as none match all are deleted

The subquery SELECT is_active FROM limit_orders WHERE is_active = 0 ORDER BY time_created DESC LIMIT 1000 returns a list of is_active values when is_active is 0. As such unless the is_active contains a value (date) that matches one or more values in the time_created column then the row(s) will deleted.

You need to compare like for like (oranges to oranges) as such you need to extract the time_created column not the is_active column from the subquery for the comparison.

So you should probably be using :-

@Query("DELETE FROM limit_orders where time_created NOT IN (SELECT time_created /*<<<<<<<<<<CHANGED COLUMN*/ FROM limit_orders WHERE is_active = 0 ORDER BY time_created DESC LIMIT 1000)")

you would probably not include the comment /*<<<<<<<<<<CHANGED COLUMN*/, this has only been included to show the change made.
if you have not got an index on the time_created column (if the time_created is the primary key column ) it would probably be best to add an index on the column.

However, I suspect the above will not do what you wish/expect.

The subquery will return 1000 rows (or up to 1000), thus the DELETE will delete all other rows (so if the table had 10000 rows you’d be left with 1000 rows (assuming unique time_created column, as discussed below)).

Rather I suspect that you want to DELETE the 1000 inactive (assuming 0 – inactive) rows thus really you want to delete the 1000 rows that have been extracted so instead of NOT IN you would want IN.

Note that if the time_created column is not UNIQUE (implied UNQIUE if it is the PRIMARY KEY @PrimaryKey in room) then other rows could be deleted. If rows can have the same time_created then you could use the rowid column instead.

The rowid column is a column that always exists (except for WITHOUT ROWID tables which room doesn’t cater for via annotations) but is hidden. You can always refer to it though. It will always be unique.
So using the sure-fire only delete the 1000 rows you could have :-
@Query("DELETE FROM limit_orders where rowid IN (SELECT rowid FROM limit_orders WHERE is_active = 0 ORDER BY time_created DESC LIMIT 1000)")

Or do you want

re-reading your question is sounds as though you may want to preserve 1000 inactive and delete the other inactive rows. Thus preserving 100 inactive rows along with all active rows.

If this is the case then the initial answer is close except that you want to also(AND) preserve the active.

As such what you would want is an additional condition to preserve the active rows:-

@Query("DELETE FROM limit_orders WHERE time_created NOT IN (SELECT time_created /*<<<<<<<<<<CHANGED COLUMN*/ FROM limit_orders WHERE is_active = 0 ORDER BY time_created DESC LIMIT 1000) /*PRESERVE ACTIVEs >>>>>>>>>>*/ AND is_active <> 0")

Advertisement

Answer