Menu
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
814
Use the relational operators != or <> to exclude rows in a WHERE clause.
The table operator except all returns the rows of the first result except those that are also in the second. As with all table operators both sides of except must have the same number of columns and their types must be compatible based on their position (see corresponding ).
One way to delete the duplicate rows but retaining the latest ones is by using MAX() function and GROUP BY clause.
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
)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
