acctman Posted March 11, 2009 Share Posted March 11, 2009 Hi I need help with a query that will cycle through the 'com_for' (userid) field and then only keep the latest 200 comments and delete everything after. So if 'com_for' userid 39 has 250 comments then delete the oldest 50 comments. The com_time field holds the time in this format 1133173361 table: rate_picture_comments fields: com_for , com_comment , com_time thanks in advance. this is a bit too advanced for me to figure out how to do without some assistance. Quote Link to comment https://forums.phpfreaks.com/topic/149020-count-and-delete-oldest-entries/ Share on other sites More sharing options...
revraz Posted March 12, 2009 Share Posted March 12, 2009 You could try something like DELETE FROM `table` WHERE `com_for` = 39 AND `com_time` = (SELECT `com_time` FROM `table` ORDER BY `com_time` DESC LIMIT 200, 18446744073709551615; Or you can use a SELECT COUNT statement to count how many rows there are and use that number as your Max. Quote Link to comment https://forums.phpfreaks.com/topic/149020-count-and-delete-oldest-entries/#findComment-782857 Share on other sites More sharing options...
kickstart Posted March 12, 2009 Share Posted March 12, 2009 Hi An alternative for you:- DELETE FROM rate_picture_comments WHERE com_for = 39 AND com_comment NOT IN (SELECT COM_COMMENT FROM rate_picture_comments WHERE com_for = 39 ORDER BY com_time DESC LIMIT 200) Or to any older ones for all "com_for":- DELETE FROM rate_picture_comments WHERE com_comment NOT IN (SELECT COM_COMMENT FROM rate_picture_comments a WHERE COM_COMMENT IN (SELECT COM_COMMENT FROM rate_picture_comments b WHERE b.com_for = a.com_for ORDER BY com_time DESC LIMIT 200)) Not directly tested either but think you can see the method. It would be rather better if the rate_picture_comments had a unique key. All the best Keith Quote Link to comment https://forums.phpfreaks.com/topic/149020-count-and-delete-oldest-entries/#findComment-782863 Share on other sites More sharing options...
acctman Posted March 12, 2009 Author Share Posted March 12, 2009 Hi An alternative for you:- DELETE FROM rate_picture_comments WHERE com_for = 39 AND com_comment NOT IN (SELECT COM_COMMENT FROM rate_picture_comments WHERE com_for = 39 ORDER BY com_time DESC LIMIT 200) Or to any older ones for all "com_for":- DELETE FROM rate_picture_comments WHERE com_comment NOT IN (SELECT COM_COMMENT FROM rate_picture_comments a WHERE COM_COMMENT IN (SELECT COM_COMMENT FROM rate_picture_comments b WHERE b.com_for = a.com_for ORDER BY com_time DESC LIMIT 200)) Not directly tested either but think you can see the method. It would be rather better if the rate_picture_comments had a unique key. All the best Keith com_id is the unique index key Quote Link to comment https://forums.phpfreaks.com/topic/149020-count-and-delete-oldest-entries/#findComment-782874 Share on other sites More sharing options...
kickstart Posted March 12, 2009 Share Posted March 12, 2009 Hi Wish that was in the original post . Try this DELETE FROM rate_picture_comments WHERE com_id NOT IN (SELECT com_id FROM rate_picture_comments a WHERE com_id IN (SELECT com_id FROM rate_picture_comments b WHERE b.com_for = a.com_for ORDER BY com_time DESC LIMIT 200)) Give that a try (but back things up first and test thoroughly). All the best Keith Quote Link to comment https://forums.phpfreaks.com/topic/149020-count-and-delete-oldest-entries/#findComment-782886 Share on other sites More sharing options...
acctman Posted March 12, 2009 Author Share Posted March 12, 2009 Hi Wish that was in the original post . Try this DELETE FROM rate_picture_comments WHERE com_id NOT IN (SELECT com_id FROM rate_picture_comments a WHERE com_id IN (SELECT com_id FROM rate_picture_comments b WHERE b.com_for = a.com_for ORDER BY com_time DESC LIMIT 200)) Give that a try (but back things up first and test thoroughly). All the best Keith this is the error i received MySQL said: #1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' I'm running mysql 4.1.22 and I ran the code in phpmyadmin Quote Link to comment https://forums.phpfreaks.com/topic/149020-count-and-delete-oldest-entries/#findComment-782903 Share on other sites More sharing options...
kickstart Posted March 12, 2009 Share Posted March 12, 2009 Hi Seems it isn't supported (yet). However there might be a work around. Not tried this but a quic search suggests that while you cannot use a limit in an IN clause, you migth be able to use it like this:- DELETE FROM rate_picture_comments WHERE com_id NOT IN (SELECT com_id FROM rate_picture_comments a WHERE com_id IN (SELECT com_id FROM (SELECT com_id FROM rate_picture_comments b WHERE b.com_for = a.com_for ORDER BY com_time DESC LIMIT 200) AS SubSelect)) All the best Keith Quote Link to comment https://forums.phpfreaks.com/topic/149020-count-and-delete-oldest-entries/#findComment-782905 Share on other sites More sharing options...
acctman Posted March 12, 2009 Author Share Posted March 12, 2009 Hi Seems it isn't supported (yet). However there might be a work around. Not tried this but a quic search suggests that while you cannot use a limit in an IN clause, you migth be able to use it like this:- DELETE FROM rate_picture_comments WHERE com_id NOT IN (SELECT com_id FROM rate_picture_comments a WHERE com_id IN (SELECT com_id FROM (SELECT com_id FROM rate_picture_comments b WHERE b.com_for = a.com_for ORDER BY com_time DESC LIMIT 200) AS SubSelect)) All the best Keith received this error MySQL said: #1109 - Unknown table 'a' in where clause i think you're getting close. should a.com_for be a.com_id? Quote Link to comment https://forums.phpfreaks.com/topic/149020-count-and-delete-oldest-entries/#findComment-782910 Share on other sites More sharing options...
kickstart Posted March 12, 2009 Share Posted March 12, 2009 Hi It should. Sorry. However not sure how to get rid of it complaining about table a being unknown. All the best Keith Quote Link to comment https://forums.phpfreaks.com/topic/149020-count-and-delete-oldest-entries/#findComment-782924 Share on other sites More sharing options...
acctman Posted March 12, 2009 Author Share Posted March 12, 2009 Hi It should. Sorry. However not sure how to get rid of it complaining about table a being unknown. All the best Keith ok i was reading up on that error, and in some cases people suggest off loading some of the process to a scripting language like PHP and looping through the field entries Quote Link to comment https://forums.phpfreaks.com/topic/149020-count-and-delete-oldest-entries/#findComment-782962 Share on other sites More sharing options...
kickstart Posted March 12, 2009 Share Posted March 12, 2009 Hi I suspect that is what you might be forced to do, which would make it far less efficient. All the best Keith Quote Link to comment https://forums.phpfreaks.com/topic/149020-count-and-delete-oldest-entries/#findComment-782984 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.