I have an application that keeps track on items and uses SQLObject python ORM. At some point of time app checks if table is not too big, and if it's more than 10000 items in table it removes first N items so it's 10000 items in the table. What is the elegant and effective way to write this down via SQLObject? All solutions i come after are slow and awkward :(.
Personally I have not used SQLOBJECT. So this is a simple delete statement based on the ordering records by inserted date, oldest N records will be removed and it's in MYSQL. Unable to set limit clause using a variable due to this bug, so I used the most direct method.
For this sampel I used the threshold as 6 as it's hard to have 10000...
Sample table:
COL1 COL2
1 112
2 0
3 20
4 10
5 100
6 30
7 200
8 108
set @table_count:=0;
set @excess_count:=0;
select count(*) into @table_count
from tablex;
select (case when @table_count > 6
then (@table_count -6)
else 0 end) into @excess_count
;
select *
from tablex
where col1 between 1 and @excess_count
order by col1 asc
;
Records selected:
COL1 COL2
1 112
2 0
Above query sorts records by col1 you may change it according to yours. So the delete query can be built upon the last select. So it's best to write a storedprocedure...as this is all one akward bunch of statements...