Certain posts on my blog get flagged for deletion automatically if specific events happen and I'm working on a cron job/script that will delete these flagged posts every night at midnight.
So far, I've been testing my queries out in the MySQL monitor and this seems to be working (the 'mfd' field--[m]arked [f]or [d]eletion--is a custom field I added at the end of the wp_posts table):
DELETE FROM wp_posts,wp_post2cat USING wp_posts LEFT JOIN wp_post2cat ON wp_posts.id = wp_post2cat.post_id WHERE wp_posts.mfd = "y"
That should be it, right? It removes the post as well as references to it in the wp_post2cat table. But somehow, I feel as though I may be missing something since I don't know the whole database WordPress installs in and out yet. Oh and BTW, I don't allow commenting on posts so I don't need to worry about references in the wp_comments table.
I guess what I'm getting at is what is the SQL query that will remove all traces of a post in one fell swoop? (if at all possible, anyway)