Jump to content

Recommended Posts

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.

Link to comment
https://forums.phpfreaks.com/topic/149020-count-and-delete-oldest-entries/
Share on other sites

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.

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

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

Hi

 

Wish that was in the original post :D .

 

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

Hi

 

Wish that was in the original post :D .

 

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

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

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?

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

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.