CODEWITHSUNDEEP
sqlsqlite
Addev
Addev

Reputation: 32233

SQL delete all rows except some ones

I have a table with the following columns (of urls):

 [id,url,visited,timestamp]
 Types:[int,string,int,long]

I want to:

Delete all urls except 10 unvisited priorizing higher timestamp (or delete all if all are visited for example)

Its posible to do that in a single query? Anyway whats the best query (queries) for doing it?

Thanks in advance

Upvotes: 24

Views: 43388

Answers (2)

Travis
Travis

Reputation: 10547

DELETE FROM tableofDeletion
WHERE
  -- Delete all items not in the following select
  -- ordered by the timestamp so we can get the top 10 
  id NOT IN (SELECT id 
             FROM tableofDeletion
             WHERE 
                 visited = 0 -- false
             ORDER BY timestamp DESC
             LIMIT 10)

I think this delivers what you're looking for.

Upvotes: 8

dizzwave
dizzwave

Reputation: 4469

I don't think TOP works in sqlite -- need to use LIMIT

DELETE FROM mytable WHERE id NOT IN ( 
   SELECT id FROM mytable  
   WHERE visited = false 
   ORDER BY timestamp DESC
   LIMIT 10  
   )

Upvotes: 34

Related Questions