Deleting unneeded rows from a table with 2 criteria
- by stormbreaker
Hello. I have a many-to-many relations table and I need to DELETE the unneeded rows.
The lastviews table's structure is:
| user (int) | document (int) | time (datetime) |
This table logs the last users which viewed the document. (user, document) is unique. I show only the last 10 views of a document and until now I deleted the unneeded like this:
DELETE FROM `lastviews` WHERE `document` = ? AND `user` NOT IN (SELECT * FROM (SELECT `user` FROM `lastviews` WHERE `document` = ? ORDER BY `time` DESC LIMIT 10) AS TAB)
However, now I need to also show the last 5 documents a user has viewed. This means I can no longer delete rows using the previous query because it might delete information I need (say a user didn't view documents in 5 minutes and the rows are deleted)
To sum up, I need to delete all the records that don't fit these 2 criterias:
SELECT ... FROM `lastviews` WHERE `document` = ? ORDER BY `time` DESC LIMIT 10
and
SELECT * FROM `lastviews` WHERE `user` = ? ORDER BY `time` DESC LIMIT 0, 5
I need the logic. Thanks in advance.
