How Do I Delete Specific Rows In Sqlite Database

I am new to android and i am having trouble understanding the delete function in sqlite database.I have created my database using the string private static final String TABLE_NAME

Solution 1:

You could use a query like this:

db.execSQL("DELETE FROM " + TABLE_NAME +
           " WHERE " + currentdate + " - " + TIMESTAMP_DB + " > 2*24*60*60");

For the update method, write the SQL expression in the WHERE clause in the whereClause parameter; whereArgs is needed only for string values:

db.update(TABLE_NAME, currentdate + " - " + TIMESTAMP_DB + " > 2*24*60*60", null);

Solution 2:

You can also do like this::

db.execSQL(String.format("DELETE FROM %s WHERE %s = %d",
                Table_NAME,Table_ColumnName,Integer.parseInt(Value)));

It will execute the statement Directly.

and as you have asked WhereClause means the ColumnNames and expression that you want to use. and Where args is String array so you need to pass argument values for Expression that you have written.

db.delete(Table_NAME, ColumnName+" = ", String[]{Value});

Solution 3:

This is very "huge" question and can have more working approaches depend on your personal requirements e.q security, performance etc.

At first i recommend to you use parametrized statements instead of your "harcoded" which are unsafe and not much human readable, so use placeholders e.q.

select * from testwhereid = ?

Second, for delete rows from table use API build-in function delete(). And as @Chintan Rathod pointed out, your delete statement is not valid statement.

Now to your question. Since you want to delete rows due to specified timestamp i suggest you to insert each row with specified date in certain date format (you can simply use SimpleDateFormat. Then there are more approaches how you can do it. I prefer at first query a table and place simple if condition if between actual date and date stored in row is difference 2 days then save his rowId. Finally, you have row's ids you want to delete.

But, right now you don't know exactly how many rows you want to delete so you need to:

1. delete records in loop (API is less than 11)
2. Create dynamically query with IN clause
3. Since Android API 11, there is method that allows delete multiple records. Here you can create "dynamic" statement due to size of ids (stored in some dynamic array).

Examples:

First approach:

List<Integer> ids = new ArrayList<Integer>();
...
for (int id: ids) {
   db.delete("table", "_id = ?", newString[] {String.valueOf(id)});
}

Second approach:

List<Integer> ids = new ArrayList<Integer>();
...
StringBuilder b = new StringBuilder("delete from table where _id IN(");
String[] whereArgs = newString[ids.size()];
int index = 0;
for (int id: ids) {
   whereArgs[index] = String.valueOf(id);
   b.append("?");
   if (index < ids.size() - 1) {
      b.append(",");
   }
   index++;
}
b.append(")");

db.execSQL(b.toString(), whereArgs);

Third approach:

Same as above but you can use SQLiteStatement:

SQLiteDatabase db; // instantiated SQLiteDatabaseSQLiteStatementstm= db.compileStatement(b.toString());
stm.executeUpdateDelete();

Note: Since you want to delete multiple records (sometimes you need to delete 10 000 and more rows) try to think about TRANSACTIONS which rapidly increase performance and security.

Solution 4:

publicvoiddeleteContact(Integer id) {
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete("AddressList",
                "id = ? ",
                newString[]{Integer.toString(id)});
}

or

you can use raw SQL query to Delete row

publicvoiddeleteContact(Integer id) {
  SQLiteDatabasedatabase=this.getWritableDatabase();
 Strings="DELETE FROM AddressList WHERE item_id=" + id;
database.execSQL(s);
}

here AddressList is your TABLE name