Draeko Draeko - 5 months ago 15
Android Question

SQlite delete all rows that has a specific string value

This code delete only single record in my Table, what i want to achieve is, delete all records that have a given string phone value. Im new to SQlite, a little help are much appreciated. Thank you in advance.

This code delete single Record sucessfully:

public void delSmsData(int id){
String deleteQuery = "DELETE FROM "+TABLE_LO_SMSDATA+" WHERE _id="+id+"";
SQLiteDatabase db = this.getWritableDatabase();
db.execSQL(deleteQuery); //delete single row by ID in a table
db.execSQL("vacuum");
db.close();
Log.e("Debug", "Successful delete of message with id no. " + id);
}


I successfuly deleted all the records with this code. Thanks to those who helped.

public void delAllSmsByPhone(String phone){
String deleteQuery = "DELETE FROM "+TABLE_LO_SMSDATA+" WHERE _phone='"+phone+"'";
SQLiteDatabase db = this.getWritableDatabase();
db.execSQL(deleteQuery);
db.execSQL("vacuum");
db.close();
}


This is my Table Structure:

String CREATE_LO_SMSDATA_TABLE = "CREATE TABLE "
+TABLE_LO_SMSDATA+"("
+KEY_ID+" INTEGER PRIMARY KEY AUTOINCREMENT,"
+KEY_EMAIL+" TEXT,"
+KEY_TARGETNAME+" TEXT,"
+KEY_SMSTYPE+" TEXT,"
+KEY_PHONE+" TEXT,"
+KEY_CONTACTNAME+" TEXT,"
+KEY_SMSBODY+" TEXT,"
+KEY_DATE+" TEXT"+")";
db.execSQL(CREATE_LO_SMSDATA_TABLE);

Answer

I don't understand you completely. Did you try to run the code?

This code is not the right approach

It is absolutely right approach. DELETE can delete any count of rows not only one. It deletes all rows for which the WHERE clause is true.

Proof:

sqlite> .tables
sms
sqlite> .schema sms
CREATE TABLE `sms` (
    `_id`   INTEGER PRIMARY KEY AUTOINCREMENT,
    `_phone`    TEXT
);
sqlite> SELECT * FROM sms;
1|+79271234567
2|+79271234567
3|+79213983722
4|+79271234567
5|+78450738422
6|+79271234567
7|+77432739417
8|+74673627246
9|+79271234567
10|+79271234567
sqlite> DELETE FROM sms WHERE _phone='+79271234567';
sqlite> SELECT * FROM sms;
3|+79213983722
5|+78450738422
7|+77432739417
8|+74673627246