CODEWITHSUNDEEP
androidsqliteandroid-sqlite
smacbeth
smacbeth

Reputation: 23

Android Sqlite: Delete first n records in a table (is deleting all rows instead)

I am using the following sql statement to delete the first 5 rows in my table:

DELETE FROM tblname WHERE `id`   IN (SELECT `id`   FROM tblname ORDER BY `id`   ASC LIMIT 5)

Instead of deleting 5 rows it deletes all of the rows.

I have also tried the following variant:

DELETE FROM tblname WHERE 'rowid' IN (SELECT 'rowid' FROM tblname ORDER BY 'rowid' ASC LIMIT ?)

I can't find any errors being generated by this. Here is the specific code I am using to perform this operation:

final SQLiteDatabase db = getWritableDatabase();
final String selectQuery = "DELETE FROM " + TABLE_NAME + " WHERE '_id' IN (SELECT '_id' FROM "+ TABLE_NAME + " ORDER BY '_id' ASC LIMIT 5)";

final Cursor cursor = db.rawQuery(selectQuery, null);

cursor.moveToFirst();
cursor.close();

Any pointers would be be much appreciated.

Upvotes: 2

Views: 1428

Answers (2)

Saic Siquot
Saic Siquot

Reputation: 6513

Well, '_id' is a constant string literal (and not a column name) on both, main query and subquery - so all records match and all records are deleted.

Here is sqlite relevant documentation. Using _id as a column name (identifier) does not need any type of surrounding quotes, but in case you want to add some, use double quote ".

Upvotes: 2

ezig
ezig

Reputation: 1229

Are you sure that '_id' should be in single quotes? In general, you don't put quotes around column names. I made a table with an integer primary key field called id and this query works as expected:

DELETE FROM tblname WHERE id IN (SELECT id FROM tblname ORDER BY id ASC LIMIT 1)

But this query deleted everything:

DELETE FROM tblname WHERE 'id' IN (SELECT 'id' FROM tblname ORDER BY 'id' ASC LIMIT 1)

Upvotes: 2

Related Questions