SQLite delete query in Android

Question

I'd need your help, I'm starting with Android databases and I have a litte problem/question for update my tables (maybe is a stupid question but is hard for me ) :

I have a table with info about videos, one of the columns is the timestamp for indentify how old is the video. I'd like to delete all rows except the top 10 with higher timestamp.

if the table name is TABLE and the (important) rows are KEY_ID, and KEY_TIMESTAMP:

How can I concat the query1:

SELECT KEY_ID FROM TABLE ORDER BY KEY_TIMESTAMP ASC LIMIT (## dont know what to put here ##)

with the query

DELETE FROM TABLE WHERE KEY_ID = query1

in Android SQLite code? what should I put in between the ## ##

http://stackoverflow.com/questions/6745496/how-can-i-limit-the-number-of-rows-in-android-sqlite-table/6745571#6745571 — Mojo Risin, Jul 19 '11 at 15:33

score 2 · Accepted Answer · answered Jul 19 '11 at 15:34

2

Try using DELETE FROM with a nested SELECT.

Not tested:

DELETE 
FROM TABLE
WHERE KEY_ID NOT IN
(
    SELECT TOP 10 KEY_ID 
        FROM TABLE 
        ORDER BY KEY_TIMESTAMP ASC
        LIMIT 10
)

answered Jul 19 '11 at 15:34

Ben Siver

2,758
1
25
42

Oh thats much better solution! thanks a lot! Could you explain me how to concat both queries in android? – Polugern Jul 19 '11 at 15:36
Not quite sure what you mean by concatenate. The query I provided should have the functionality you were explaining without the need to add anything else to it. – Ben Siver Jul 19 '11 at 15:38
what i mean is: db.delete(TABLE, KEY_ID+" NOT IN "+ ##something##) or should I use execSQL (String sql) ? thanks – Polugern Jul 19 '11 at 15:41
It might be easier for you to declare the query as a String and then call database.execSQL() with the query. – Ben Siver Jul 19 '11 at 15:44
No problem! Good luck to you – Ben Siver Jul 19 '11 at 15:51

SQLite delete query in Android

1 Answers1