Android Delete Query

Question

I'm attempting to delete all rows in one table that do not have a corresponding ID in another table. Since apparently SQLite does not support joins in deletes I am trying to do something along these lines:

    DELETE FROM my_table WHERE my_id NOT IN (SELECT _id FROM my_table2);

However, I apparently can not use rawQuery since it returns a cursor so I have to use the delete function. I'm having some trouble getting this working. Here is the query I'm trying:

    mDb.delete("my_table", "my_id NOT IN ?", new String[]{"(SELECT _id FROM my_table2)"});

Thanks.

score 24 · Accepted Answer · answered Jun 14 '11 at 00:29

24

You shouldn't use a .rawQuery, as you stated, but you can use .execSQL() to accomplish it. I regularly use it for deletions myself.

answered Jun 14 '11 at 00:29

mah

39,056
9
76
93

@mah can you plz explain how to use .execSQL for deletion ...i tried it threw error to me .Plz – Rauf Sep 12 '13 at 10:24
The doc on `.execSQL` says "Execute a single SQL statement that is NOT a SELECT/INSERT/UPDATE/DELETE." Why does this still work for those statements? – Nii Laryea Dec 04 '16 at 03:23

score 1 · Answer 2 · answered Jun 14 '11 at 00:15

1

I think the only way is to execute your select and dynamically combine your WHERE clause.

answered Jun 14 '11 at 00:15

Konstantin Milyutin

11,946
11
59
85

score 0 · Answer 3 · edited Apr 07 '21 at 11:31

0

You have to use execSQL instead rawQuery because rawQuery is used for the statement that return data and execSQL is used for the statements that don't return data like DELETE

edited Apr 07 '21 at 11:31

marc_s

732,580
175
1,330
1,459

answered Nov 25 '20 at 09:56

Muhamad Hafiz

1
1
1

Android Delete Query

3 Answers3

Linked

Related