Geek Answers Handbook

Sql to delete records older than XXX if more than YY rows exist

Suppose a table with the following columns:

pri_id , item_id , comment , date

I want to have an SQL query that will delete any records for a specific item_id that is older than a given date, but only as long as item_id are more than 15 rows for this item_id .

This will be used to clear comment entries older than 1 year for items, but I still want to save at least 15 entries at any given time. Thus, if I had one comment for 10 years, it would never have been deleted, but if I had 100 comments in the last 5 days, I would save only the most recent 15 entries. These, of course, are arbitrary record numbers and dates for this example.

I would like to find a very general way to do this that will work in mysql, oracle, postgres, etc. I am using the phod adodb library to abstract the DB, so I would like it to work well with this if possible.

+6
sql datetime
source share
3 answers

Something like this should work for you:

 delete from MyTable where item_id in ( select item_id from MyTable group by item_id having count(item_id) > 15 ) and Date < @tDate
+5
source share

You want to keep at least 15 of them always, right? So:

  DELETE FROM CommentTable WHERE CommentId NOT IN ( SELECT TOP 15 CommentId FROM CommentTable WHERE ItemId=@ItemId AND CommentDate < @Date ORDER BY CommentDate DESC ) AND ItemId=@ItemId AND CommentDate < @Date
+2
source share

Is this what you are looking for?

 DELETE [MyTable] WHERE [item_id] = 100 and (SELECT COUNT(*) FROM [MyTable] WHERE [item_id] = 100) > 15

I am an MS SQL Server guy, but I think it should work elsewhere.

0
source share

More articles:


All Articles