MySQL: Delete WordPress Custom Posts Within a Date Range

This MySQL query deletes all WordPress posts of one custom post type whose post date falls within a date range, together with their custom fields, category and tag assignments and comments. It’s useful when you need to remove a large batch of posts at once. Deleting them page by page in the admin would take too long.

Run it in the SQL tab of phpMyAdmin or in any MySQL client connected to your WordPress database. Back up the database first: a DELETE can’t be undone. If you don’t have backups set up yet, see the backup step in this WordPress security checklist.

DELETE p, pm, tr, c, cm
FROM wp_posts p
LEFT JOIN wp_postmeta pm ON pm.post_id = p.ID
LEFT JOIN wp_term_relationships tr ON tr.object_id = p.ID
LEFT JOIN wp_comments c ON c.comment_post_ID = p.ID
LEFT JOIN wp_commentmeta cm ON cm.comment_id = c.comment_ID
WHERE p.post_type = 'cad'
AND
p.post_date > '2022-03-29' AND p.post_date < '2022-04-03'

How it works

• DELETE p, pm, tr, c, cm deletes from five tables in one statement. The posts are p, and each LEFT JOIN adds the rows that belong to them: custom fields in wp_postmeta (pm), category and tag assignments in wp_term_relationships (tr), comments in wp_comments (c) and comment meta in wp_commentmeta (cm). A post with no meta, terms or comments goes too, because a LEFT JOIN doesn’t need a match.
• p.post_type = 'cad' limits the query to the cad post type. Replace it with your post type’s key, the name you gave it in register_post_type(). It can differ from the slug in its URLs.
• post_date holds the post date in the site’s time zone (post_date_gmt has the UTC version). A date without a time means midnight. So this range matches posts from just after midnight on March 29, 2022, to the end of April 2. Posts dated April 3 stay untouched.
• wp_ is the default table prefix. If $table_prefix in your wp-config.php is different, change all five table names.
• The query doesn’t check post_status, so drafts, trashed posts and published posts in the range are all deleted.

Before you delete posts in a date range with MySQL

Preview the result first: replace the first line, DELETE p, pm, tr, c, cm, with SELECT DISTINCT p.ID, p.post_title, p.post_date and run the query. It lists each post the delete would remove. If the posts are the ones you expect, run the delete.

WordPress’s own cleanup doesn’t run when you delete with SQL, so two things stay behind. Revisions are separate rows in wp_posts with the post type revision and the deleted post’s ID in post_parent; your post type only has them if it supports revisions. The post counts of the categories, tags or other terms the posts had aren’t updated either; with WP-CLI, wp term recount followed by the taxonomy names, for example wp term recount category post_tag, recalculates them. Deleting posts through WordPress, in the admin or with wp_delete_post(), handles all of this, so prefer it when the number of posts allows.

Related articles

• changing a custom post type’s URL slug: why the URL slug and the post type key can differ