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?

Solution 1

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

Solution 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

Solution 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.

Solution 4

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

View more solutions

Comments

[LuckyMe]

I just killed 4 hours on this dumb a** issue, they should have this clearly stated in the documentation, what a waste of time.

[Noumenon]

I like how your question contains an answer to my delete problems, very efficient!

[kgrevehagen]

Thanks for finding this. This should definitely be documented! Shouldn't be like this, I think, but at least it works now.

[Vincent]

Do not forget to add the db.close() in the same routine, otherwise the data will also NOT be deleted.

[fast3r]

It's unbelievable that, 6 years later, this "behaviour" is still present and undocumented.

[marlar]

I would have used a straight delete query if it ẃas possible, but it does not support such a complicated sql statement. It cannot operate on joins and sub queries, as far as I know at least.

[SBerg413]

Then use the 2nd option I suggested.

[marlar]

I certainly will look at that option, but still I am curious as of why the moveToFirst is needed. I have seen many examples of perform delete queries with rawQuery and none of them used the moveToFirst.

[Androiderson]

How is it any different from the older answers? You've just copied @mopurizwarriors words.

[Ewoks]

Please read carefully again... it is not about running successfully or not. It is about not running at all, but preparing query before some of methods on cursor are executed. Additionally this was not try/fail method of figuring out answer but saying that it meant to work like that.

[Androiderson]

Then you should elaborate on your answer, because Query gets executed only when we call some method on the cursor and rawQuery actually doesn't execute til u call some method on returned cursor read the same dude. Anyone will say you've just copied that since your answer is 2 years old.

[Ewoks]

Not sure what you are trying to score here here mate. If you think it can be explained better feel free to improve it or write better answer, I would be more than happy to help if you can't do it on your own. Not really sure how otherwise I could help you...