code_junkie

sql statement to delete records older than XXX as long as there are more than YY rows

Nov 11th, 2011
82
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 0.58 KB | None | 0 0
  1. delete
  2. from
  3. MyTable
  4. where
  5. item_id in
  6. (
  7. select
  8. item_id
  9. from
  10. MyTable
  11. group by
  12. item_id
  13. having
  14. count(item_id) > 15
  15. )
  16. and
  17. Date < @tDate
  18.  
  19. DELETE
  20. FROM CommentTable
  21. WHERE CommentId NOT IN (
  22. SELECT TOP 15 CommentId
  23. FROM CommentTable
  24. WHERE ItemId=@ItemId
  25. AND CommentDate < @Date
  26. ORDER BY CommentDate DESC
  27. )
  28. AND ItemId=@ItemId
  29. AND CommentDate < @Date
  30.  
  31. DELETE
  32. [MyTable]
  33. WHERE
  34. [item_id] = 100 and
  35. (SELECT COUNT(*) FROM [MyTable] WHERE [item_id] = 100) > 15
Add Comment
Please, Sign In to add comment