Solved

Make MySql auto delete old records in a table

Posted on 2011-02-21
6
952 Views
Last Modified: 2013-12-24
Hi,

is it possible to make mysql limit the number of records in table to 100 and when a new record is inserted delete the eldest to ensure the table doesn't grow too much?

I am using Coldfusion so could run a bunch of sql queries to achieve it but was hoping there was a built in function or trigger that can handle this
0
Comment
Question by:Lmillard
6 Comments
 
LVL 20

Expert Comment

by:Proculopsis
ID: 34942062

If you always select top 100 ordered descending by age then your result set will always be manageable.  Then from time-to-time you can perform maintenance to remove any surplus rows or you could create an insert trigger to maintain on-the-fly.
0
 

Author Comment

by:Lmillard
ID: 34942172
Hi, this is inserting records into a table that is history orientated so i would like it to be in the background as its not about retrieving the right amount of records, more keeping the size under control. I am unfamiliar with triggers so was hoping there was a setting for the table
0
 
LVL 20

Expert Comment

by:Proculopsis
ID: 34942329

//Try something like this:

CREATE TRIGGER purgeOld
BEFORE INSERT ON myTable
DELETE FROM myTable WHERE ......
0
 
LVL 82

Expert Comment

by:leakim971
ID: 34943490
A trigger will not work with MySQL : http://forums.mysql.com/read.php?99,122354,122505#msg-122505

I think you need to do that with the language used on the server side
0
 
LVL 39

Accepted Solution

by:
gdemaria earned 500 total points
ID: 34945059

After insert, or periodically (nightly batch?) you could do a delete statement like this..


delete from myTable where ID not in (
  select top 100 ID
  from myTable
  order by theDate desc)
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
If you don't have the right permissions set for your WordPress location in IIS, you won't be able to perform automatic updates. Here's how to fix the problem.
Concerto provides fully managed cloud services and the expertise to provide an easy and reliable route to the cloud. Our best-in-class solutions help you address the toughest IT challenges, find new efficiencies and deliver the best application expe…
A company’s greatest vulnerability is their email. CEO fraud, ransomware and spear phishing attacks are the no1 threat to a company’s security. Cybercrime is responsible for the largest loss of money to companies today with losses projected to r…

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now