Why is such DELETE query not working?

Question

This is my method to delete a row from the database where appointment_date is equal to a date that was passed in

public void deleteAllAppointments(String date) {
        SQLiteDatabase db = this.getWritableDatabase();

        String deleteAllQuery = "DELETE FROM " + TABLE_APPOINTMENTS + " WHERE appointment_date = '" + date + "'";
        db.rawQuery(deleteAllQuery, null);
        Log.d("Query: ", deleteAllQuery);
    }

I then use it like this

    //Database (DatabaseHandler is the one that contains all database methods)
    final DatabaseHandler database = new DatabaseHandler(this);

    //This happens when button is clicked, it is tested an executes with every chick,   
    //@param selectedDate is a string like "18/03/2014"

    database.deleteAllAppointments(selectedDate);

It executes and query looks like this

DELETE FROM appointments WHERE appointment_date = '18/03/2014'

However row with appointment_date = '18/03/2014' is not deleted.

I'm sure database is set up correctly as I have working methods with it and all information is received from there in correct format.

NOTE: Adding "*" to "DELETE * FROM..." returns a fatal syntax error.

The error with adding the * occurs because that's not proper sql syntax. To delete the whole table you just say DELETE FROM TABLE_NAME, the * is only used in select query — Rarw, Mar 18 '14 at 19:28

score 3 · Accepted Answer · edited May 23 '17 at 12:30

3

rawQuery() just compiles the SQL but does not run it. To actually run it, use either execSQL() or call one of the moveTo...() methods on the cursor returned by rawQuery().

For further info, see What is the correct way to do inserts/updates/deletes in Android SQLiteDatabase using a query string?

edited May 23 '17 at 12:30

Community

1
1

answered Mar 18 '14 at 19:29

laalto

150,114
66
286
303

Could you please link me to documentation of moveTo..(), tried googling it nothing useful came up – Ilja Mar 18 '14 at 19:30
Weird, the docs say `rawQuery`: "Runs the provided SQL and returns a Cursor over the result set." Seems to indicate the query is executed(?) – Cody Caughlan Mar 18 '14 at 19:36
There really isn't that good documentation. I linked to an older answer of mine where I explain things in more detail. – laalto Mar 18 '14 at 19:37

score 1 · Answer 2 · answered Mar 18 '14 at 19:27

For tasks such as insert or delete there are really great "convenience methods" like the [delete method](http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html#delete(java.lang.String, java.lang.String, java.lang.String[])) already built in to the database.

public int delete (String table, String whereClause, String[] whereArgs)

As to why your current approach would fail, it could be something as simple the format of the column you're trying to delete not matching (e.g. you have created the table as a date value and not a string).

In any case, using the built in delete method is easier because it will notify you when it fails by returning the number of rows affected by the delete. rawQuery just returns a cursor, which you would then have to get the result from to see if it worked.

score 1 · Answer 3 · answered Mar 18 '14 at 19:34

Are you sure your data value is in European format of day/month/year ala your query value of 18/03/2014 and maybe its not US style of month/day/year: 03/18/2014.

Not trying to be US-centric but that was my first thought.

Otherwise, definitely look at SQLiteDatabase.delete:

http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html#delete(java.lang.String, java.lang.String, java.lang.String[])

Why is such DELETE query not working?

3 Answers3