CODEWITHSUNDEEP
androidsqlsqliteormlite
no9
no9

Reputation: 6544

Little help putting together a delete statement in SqlLite using OrmLite

I have trouble putting together a delete statement in my Android application (I am using OrmLite).

I have a table filled with records. Two of the fields are "dateCreated" (type Date) and "imageSize" (type int). In my code I have a method free(int size). This method tells me that I have to delete oldest records from my table that sum "imageSize" <= size.

For instance .. i get parameter 1000. Each record has value lets say 100. That means i have to delete 10 of the oldest records.

Can some one please provide me with optimal raw SQL statement or even better an OrmLite code for this?

I would be most gratefull.

Upvotes: 1

Views: 511

Answers (2)

Gray
Gray

Reputation: 116878

Unfortunately, you can't do this with a single SQL statement. There is no way to say

select records until their sum is less than X

You could doing multiple queries until you found the oldest records whose sum is less than X but it would take a number of separate SQL calls.

I'd recommend selecting the last X images with their sizes and then doing a delete of the right number of images using a DELETE ... IN .... Here's the pseudo code:

while (true) {
   SELECT id, imageSize FROM yourtable ORDER BY dateCreated DESC LIMIT 10;
   find the images from the bottom whose sum(imageSize) <= parameter
   delete the found images
   break if you exceed the parameter otherwise loop and get the next 10
}

Upvotes: 1

sudheshna
sudheshna

Reputation: 1150

try this,

DELETE FROM yourtable WHERE imageSize <= (SELECT SUM(ImageSize) FROM yourtable)

Use the same parameter for your function

Upvotes: 0

Related Questions