Have you tried the bulk delete feature in the admin area?
SQL is possible too, but I’d need to know more about what exactly you want to delete, i.e. is it just recurrences from the past?
Thread Starter
ian72
(@ian72)
Hey Marcus, thanks for replying.
For events that HAVE passed, but are still part of a recurring set of events that have NOT completely passed, I get the impression that I have to Detach the event before I can bulk delete or the whole lot get deleted?
SQL- I’m looking to delete events that with an end_date before a specific date (today for example) and as a separate query, events whose start_date is after a certain date (next year for example)
Before I realised EM’d changed to custom posts (doh!), I was doing something like this in phpMyadmin
DELETE FROM em_events WHERE event_end_date < 2012-10-03
and
DELETE FROM em_events WHERE event_start_date > 2013-10-03
Cheers
You’d want something like
(disclaimer – hand written, not tested, use at own risk)
The select statement you’ll nest rmains the same so I’ll paste it once, replace the … in the further statements
SELECT post_id FROM wp_em_events WHERE event_end_date < NOW() AND recurrence_id > 0
that gets all your past events that are recurrences
DELETE FROM wp_postmeta WHERE object_id IN (...)
DELETE FROM wp_postmeta WHERE post_id IN (...)
DELETE FROM wp_posts WHERE ID IN (...)
DELETE FROM wp_em_events WHERE event_end_date < NOW() AND recurrence_id > 0
I think that clears all the relevant info. If you find any errors/typos would appreciate a heads up to save grief for others 🙂
Thread Starter
ian72
(@ian72)
I’ve done as suggested but Im still not deleting the previous events properly.
You were pretty much bang with the statements apart from the first DELETE statement. because object_id doesn’t exist as a column in wp_em_events
Am I missing something here?
Although this has deleted a whole bunch of stuff (see below) I still have lots of previous events showing in the admin.
So, I looked at the post_id in WordPress for one of the previous events, and can see that there are still many instances in wp_postmeta with the related post_id. They have not been deleted properly.
Looking at the sql calls, I don’t think the logic is quite right, but Im too daft to work out the right thing.
It would help if I knew which tables were in operation – is wp_em_events even still in the mix? Thought wp_posts had taken over.
For the record here’s what I did
*I tried all these out first replacing DELETE with SELECT * to check the syntax was OK and the tables existed etc.
1.
DELETE
FROM wp_postmeta
WHERE post_id
IN (
SELECT post_id
FROM wp_em_events
WHERE event_end_date < NOW( )
AND recurrence_id >0
)
Deleted rows: 25785 (Query took 5.9513 sec)
2.
DELETE
FROM wp_posts
WHERE ID
IN (
SELECT post_id
FROM wp_em_events
WHERE event_end_date < NOW( )
AND recurrence_id >0
)
Deleted rows: 849 (Query took 0.1698 sec)
3.
DELETE FROM wp_em_events WHERE event_end_date < NOW() AND recurrence_id > 0
Deleted rows: 887 (Query took 0.1382 sec)
from the looks of it there’s a disconnect between queries 3 and 1, they should be the same rows affected.
