remove-old-campaigns.sql
· 1.1 KiB · MySQL
Raw
-- Remove campaigns older than 6 months from phpList including all stats.
-- Start transaction
START TRANSACTION;
-- Delete user-message links for campaigns older than 6 months
DELETE FROM phplist_usermessage
WHERE messageid IN (
SELECT id FROM phplist_message WHERE DATE_ADD(entered, INTERVAL 6 MONTH) < NOW()
);
-- Delete link tracking data for campaigns older than 6 months
DELETE FROM phplist_linktrack
WHERE messageid IN (
SELECT id FROM phplist_message WHERE DATE_ADD(entered, INTERVAL 6 MONTH) < NOW()
);
-- Delete bounce data for campaigns older than 6 months
DELETE FROM phplist_bounce
WHERE messageid IN (
SELECT id FROM phplist_message WHERE DATE_ADD(entered, INTERVAL 6 MONTH) < NOW()
);
-- Delete event log entries for campaigns older than 6 months
DELETE FROM phplist_eventlog
WHERE messageid IN (
SELECT id FROM phplist_message WHERE DATE_ADD(entered, INTERVAL 6 MONTH) < NOW()
);
-- Delete the campaigns themselves that are older than 6 months
DELETE FROM phplist_message
WHERE DATE_ADD(entered, INTERVAL 6 MONTH) < NOW();
-- Commit the transaction
COMMIT;
| 1 | -- Remove campaigns older than 6 months from phpList including all stats. |
| 2 | -- Start transaction |
| 3 | START TRANSACTION; |
| 4 | |
| 5 | -- Delete user-message links for campaigns older than 6 months |
| 6 | DELETE FROM phplist_usermessage |
| 7 | WHERE messageid IN ( |
| 8 | SELECT id FROM phplist_message WHERE DATE_ADD(entered, INTERVAL 6 MONTH) < NOW() |
| 9 | ); |
| 10 | |
| 11 | -- Delete link tracking data for campaigns older than 6 months |
| 12 | DELETE FROM phplist_linktrack |
| 13 | WHERE messageid IN ( |
| 14 | SELECT id FROM phplist_message WHERE DATE_ADD(entered, INTERVAL 6 MONTH) < NOW() |
| 15 | ); |
| 16 | |
| 17 | -- Delete bounce data for campaigns older than 6 months |
| 18 | DELETE FROM phplist_bounce |
| 19 | WHERE messageid IN ( |
| 20 | SELECT id FROM phplist_message WHERE DATE_ADD(entered, INTERVAL 6 MONTH) < NOW() |
| 21 | ); |
| 22 | |
| 23 | -- Delete event log entries for campaigns older than 6 months |
| 24 | DELETE FROM phplist_eventlog |
| 25 | WHERE messageid IN ( |
| 26 | SELECT id FROM phplist_message WHERE DATE_ADD(entered, INTERVAL 6 MONTH) < NOW() |
| 27 | ); |
| 28 | |
| 29 | -- Delete the campaigns themselves that are older than 6 months |
| 30 | DELETE FROM phplist_message |
| 31 | WHERE DATE_ADD(entered, INTERVAL 6 MONTH) < NOW(); |
| 32 | |
| 33 | -- Commit the transaction |
| 34 | COMMIT; |
| 35 |