Delete first N rows in android sqlite database

Question

Please let me know how to delete n-rows in android sqlite database. I used this code:

   String ALTER_TBL ="delete from " + MYDATABASE_TABLE +
         "where"+KEY_ID+"in (select top 3"+ KEY_ID +"from"+ MYDATABASE_TABLE+"order by _id );";

          sqLiteDatabase.execSQL(ALTER_TBL);

But it shows an error.

03-21 13:19:39.217: INFO/Database(1616): sqlite returned: error code = 1, msg = near "in": syntax error
03-21 13:19:39.226: ERROR/Database(1616): Failure 1 (near "in": syntax error) on 0x23fed8 when preparing 'delete from detail1where_id in (select top 3_idfromdetail1order by _id );'.

Answer 1

String ALTER_TBL ="delete from " + MYDATABASE_TABLE +
     " where "+KEY_ID+" in (select "+ KEY_ID +" from "+ MYDATABASE_TABLE+" order by _id LIMIT 3);";

• there is no "top 3" command in sqlite I know of, you have to add a limit
• watch out for spaces when you add strings together : "delete from" + TABLE + "where" = "delete frommytablewhere"

---

This approach uses two steps to delete the first N rows.

1. Find the first N rows:

   SELECT id_column FROM table_name ORDER BY id_column LIMIT 3

   The result is a list of ids that represent the first N (here: 3) rows. The ORDER BY part is important since SQLite does not guarantee any order without that clause. Without ORDER BY the statement could delete 3 random rows.

2. Delete any row from the table that matches the list of ids:

   DELETE FROM table_name WHERE id_column IN ( {Result of step 1} )

   If the result from step 1 is empty nothing will happen, if there are less than N rows just these will be deleted.

   It is important to note that the id_column has to be unique, otherwise more than the intended rows will be deleted. In case the column that is used for ordering is not unique the whole statement can be changed to DELETE FROM table_name WHERE unique_column IN (SELECT unique_column FROM table_name ORDER BY sort_column LIMIT 3). Hint: SQLite's ROWID is a good candidate for unique_column when deleting on tables (may not work when deleting on views - not sure here).

To delete the last N rows the sort order has to be reversed to descending (DESC):

DELETE FROM table_name WHERE unique_column IN (
    SELECT unique_column FROM table_name ORDER BY sort_column DESC LIMIT 3
  )

To delete the N^th to M^th row the LIMIT clause can be extended by an OFFSET. Example below would skip the first 2 rows and return / delete the next 3.

SELECT unique_column FROM table_name ORDER BY sort_column LIMIT 3 OFFSET 2

Setting the LIMIT to a negative value (e.g. LIMIT -1 OFFSET 2) would return all rows besides the first 2 resulting in deletion of everything but the first 2 rows - that could also be accomplished by turning the SELECT .. WHERE .. IN () into SELECT .. WHERE .. NOT IN ()

---

SQLite has an option to enable the ORDER BY x LIMIT n part directly in the DELETE statement without a sub-query. That option is not enabled on Android and can't be activated but this might be of interest to people using SQLite on other systems:

 DELETE FROM table_name ORDER BY sort_column LIMIT 3

Answer 2

It seems that you've missed some spaces:

"where"+KEY_ID+"in..

must be:

"where "+KEY_ID+" in...

Furthermore you need to use the limit statement instead of top: