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 ## ##

stackoverflow.com/questions/6745496/…

Mojo Risin
– Mojo Risin

2011-07-19 15:33:45 +00:00
Commented Jul 19, 2011 at 15:33 — Mojo Risin
– Mojo Risin, Commented Jul 19, 2011 at 15:33

Ben Siver · Accepted Answer · 2011-07-19 15:34:38Z

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, 2011 at 15:34

Ben Siver

2,9881 gold badge28 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Polugern Over a year ago

Oh thats much better solution! thanks a lot! Could you explain me how to concat both queries in android?

Ben Siver Over a year ago

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.

Polugern Over a year ago

what i mean is: db.delete(TABLE, KEY_ID+" NOT IN "+ ##something##) or should I use execSQL (String sql) ? thanks

Ben Siver Over a year ago

It might be easier for you to declare the query as a String and then call database.execSQL() with the query.

Ben Siver Over a year ago

No problem! Good luck to you

Collectives™ on Stack Overflow

SQLite delete query in Android

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related