Why does a delete rawQuery need a moveToFirst in order to actually delete the rows?

Question

I have been struggling for hours trying to debug why the following delete query actually didn't delete anything even if the exact same query on the exact same database worked fine in Firefox' SQLite Manager:

String deleteSql = "DELETE FROM showsummary WHERE url IN (SELECT url FROM showsummary JOIN article_categories USING (url) WHERE categoryid=20 AND title LIKE 'page=%')";
mDb.rawQuery(deleteSql, null);

Since it is a bit complicated with both a JOIN and a sub query my thoughts circled around some limitations in Android's sqlite implementation regarding sub queries so I tried simplifying the query. But still it didn't delete anything.

Then I changed it to a select query (just replaced DELETE with SELECT *) and that worked. So probably it was not the join or the sub query that was the culprit after all.

In order to test the select query I had added a moveToFirst() to the returned cursor:

mDb.rawQuery(deleteSql, null).moveToFirst();

When I later changed it back to a delete query again, I forgot to remove the moveToFirst() and then it worked!

It's nice that it works now, but I am very confused why it is necessary to move the cursor in order to actually delete anything. Is this by design or is it a bug?

Answer 1

I cannot answer the why, but another solution is to use .execSQL(String) as posted here

Answer 2

A rawQuery returns a Cursor of a result set, which is just a reference to the query results. You should just be using a straight delete() call. Take a look at the documentation:

http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html

or an SQLiteStatement:

http://developer.android.com/reference/android/database/sqlite/SQLiteStatement.html

Answer 3

In my case also, the same thing has happened for DELETE, It ran successfully after calling "cursor.moveToFirst()". Same is the case with INSERT and UPDATE queries also. I have also observed that calling any method on the cursor for the above mentioned queries I have got the results correctly. Without calling any method of the cursor the desired effect is taking place. So, I think the answer to your question is : Query gets executed only when we call some method on the cursor.

Answer 4

The answer why is because rawQuery actually doesn't execute til u call some method on returned cursor. moveToFirst, isAfterLast ..