• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1334
  • Last Modified:

Make MySql auto delete old records in a table

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
Lmillard
Asked:
Lmillard
1 Solution
 
ProculopsisCommented:

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
 
LmillardAuthor Commented:
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
 
ProculopsisCommented:

//Try something like this:

CREATE TRIGGER purgeOld
BEFORE INSERT ON myTable
DELETE FROM myTable WHERE ......
0
 
leakim971PluritechnicianCommented:
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
 
gdemariaCommented:

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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now