Delete rows or Reset columns older than X days in MySQL
on June 16th, 2011 at 3:09 am
If you need to delete rows or reset data in columns older than X days using MySQL, then there is an easy way to do it:
Deleting Rows from your table X days old:
DELETE FROM mytable WHERE recorededDate < DATE_SUB(NOW(), INTERVAL 25 DAY);
Resetting Columns from your table X days old:
UPDATE mytable SET myColumn1 = 0, mycolumn2 = 0 WHERE recorededDate < DATE_SUB(NOW(), INTERVAL 14 DAY);
You could also use:
INTERVAL 1 MONTH
INTERVAL 1 YEAR
INTERVAL 1 MINUTE
INTERVAL 1 WEEK + INTERVAL 1 MONTH - INTERVAL 1 YEAR
NOTE: If an UPDATE statement is designed to update multiple rows, and an error is encountered attempting to update some of those rows, the entire update is canceled and any rows that had been changed are reverted to their original values. Use of the IGNORE keyword will cause the update to continue, simply skipping any rows which presented a problem.
Stumble Upon
Del.icio.us
Buzz