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 );'.

you can not use delete statement with select statement in single query. — Lucifer
– Lucifer, Commented Mar 21, 2012 at 7:48
@Lucifer actually you can. But you can't put a delete inside a select. see sqlite.org/lang_expr.html the expression is the part you can put after "where" — zapl
– zapl, Commented Mar 21, 2012 at 8:14

zapl · Accepted Answer · 2012-08-14 11:37:14Z

50

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.

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.
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

edited Aug 14, 2012 at 11:37

answered Mar 21, 2012 at 7:54

zapl

64.1k10 gold badges126 silver badges160 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user933909 Over a year ago

03-21 13:26:33.716: INFO/Database(1647): sqlite returned: error code = 1, msg = near "_id": syntax error 03-21 13:26:33.737: ERROR/Database(1647): Failure 1 (near "_id": syntax error) on 0xd5cd0 when preparing 'delete from detail1 where _id in (select _id from detail1order by _id LIMIT 3);'.

zapl Over a year ago

add the missing space before order. You have "detail1order"

Adil Soomro Over a year ago

@user933909: if this answer helped you, mark it as correct. (i.e tick mark from left)

Tim · Accepted Answer · 2012-03-21 07:55:26Z

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:

answered Mar 21, 2012 at 7:55

Tim

6,7102 gold badges27 silver badges30 bronze badges

Comments

Sathyajith Bhat · Accepted Answer · 2012-03-21 08:28:23Z

2

I'll do:

db.delete(MYDATABASE_TABLE, "KEY_ID > "+ value, null);

edited Mar 21, 2012 at 8:28

Sathyajith Bhat

22k22 gold badges102 silver badges141 bronze badges

answered Mar 21, 2012 at 8:03

user1256477

11.2k7 gold badges41 silver badges62 bronze badges

Comments

Android · Accepted Answer · 2012-03-21 08:32:16Z

2

you can try this code

int id;

public void deleteRow(int id) { 
  myDataBase.delete(TABLE_NAME, KEY_ID + "=" + id, null);
}

String id;

public void deleteRow(String id) {  
      myDataBase.delete(TABLE_NAME, KEY_ID + "=\" " + id+"\"", null);
    }

answered Mar 21, 2012 at 8:32

Android

1,4279 silver badges11 bronze badges

Comments

zeeawan · Accepted Answer · 2018-02-08 13:19:31Z

2

Delete first N (100) rows in sqlite database

Delete from table WHERE id IN 
(SELECT id FROM table limit 100)

answered Feb 8, 2018 at 13:19

zeeawan

6,9352 gold badges54 silver badges56 bronze badges

Comments

Avi Kumar · Accepted Answer · 2012-03-21 09:29:24Z

It is a bit long procedure but you can do it like this

first get the ids column of table from which which you want to delete certain values

public Cursor KEY_IDS() {
    Cursor mCursor = db.rawQuery("SELECT KEYID " +
                                     " FROM MYDATABASE_TABLE ;", null);


            if (mCursor != null)
            {
            mCursor.moveToFirst();
            }

            return mCursor;
}

Collect it in an array list

ArrayList<String> first = new ArrayList<String>();

cursor1 = db.KEY_IDS();
            cursor1.moveToFirst();
            startManagingCursor(cursor1);

            for (int i = 0; i < cursor1.getCount(); i++) {

                reciv1 = cursor1.getString(cursor1
                        .getColumnIndex(DBManager.Player_Name));

second.add(reciv1);

}

and the fire delete query

for(int i = 0 ;i<second.size(); i++)
{
db.delete(MYDATABASE_TABLE KEYID +"=" + second.get(i) , null);

}

Heartine Lazar · Accepted Answer · 2014-10-01 11:09:59Z

0

You can make use of the following mode: (in addition to the response provided by "zapl").

**DELETE FROM {Table-X} WHERE _ID NOT IN 
 (SELECT _ID FROM {Table-X} ORDER BY _ID DESC/ASC LIMIT (SELECT {Limit-Column} FROM {SpecificationTable})  );**

Where {Table-X} refers to the table you want to delete, _ID is the main unique-column DESC/ASC - Based on whether you want to delete the top records or the last records, and finally in the "LIMIT" clause, we provide the "n" factor using another query, which calls in the {Limit-Column} from {SpecificationTable}: Which holds the value against which you want to delete them.

Hope this helps out someone.

Happy Coding.

answered Oct 1, 2014 at 11:09

Heartine Lazar

1538 bronze badges

Collectives™ on Stack Overflow

Delete first N rows in android sqlite database

7 Answers 7

3 Comments

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

3 Comments

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related