Geek Answers Handbook

Delete first N rows in android sqlite database

Please let me know how to remove 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 );'.
+13
source share
7 answers
String ALTER_TBL ="delete from " + MYDATABASE_TABLE +
     " where "+KEY_ID+" in (select "+ KEY_ID +" from "+ MYDATABASE_TABLE+" order by _id LIMIT 3);";
  • in sqlite that I know about, there is no "top 3" command, you need to add a limit
  • notice the spaces when you add lines together: "delete from" + TABLE + "where" = "delete frommytablewhere"

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

  • Find the first N lines:

    SELECT id_column FROM table_name ORDER BY id_column LIMIT 3

    The result is a list of identifiers that represent the first N (here: 3) lines. Part ORDER BYis important because SQLite does not guarantee any order without this sentence. Without an ORDER BYoperator can delete 3 random lines.

  • , :

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

    1 , , N , .

    , id_column , , . , , , DELETE FROM table_name WHERE unique_column IN (SELECT unique_column FROM table_name ORDER BY sort_column LIMIT 3). : SQLite ROWID unique_column ( - ​​ ).

N last, (DESC):

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

N th M th LIMIT OFFSET. 2 / 3.

SELECT unique_column FROM table_name ORDER BY sort_column LIMIT 3 OFFSET 2

LIMIT (, LIMIT -1 OFFSET 2) , 2, , , SELECT .. WHERE .. IN () SELECT .. WHERE .. NOT IN ()

SQLite , ORDER BY x LIMIT n DELETE . Android , , SQLite :

 DELETE FROM table_name ORDER BY sort_column LIMIT 3
+36

, :

"where"+KEY_ID+"in..

:

"where "+KEY_ID+" in...

, limit :

+2

:

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

int id;

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


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

, ,

ids ,

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


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

            return mCursor;
}


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

}


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

}
+1

: ( , "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})  );**

{t-X} , , _ID DESC/ASC. , , , "LIMIT", "n", , {Limit-Column} { SpecificationTable}: , .

, -.

.

0

N (100) sqlite

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

More articles:


All Articles