Delete postmeta and posts with one SQL query

2 Views

I have 2 SQL queries for deleting posts and meta data from my WordPress database. I can run these queries in sequence, but I want to make 1 query for doing the whole job in one time.

Delete posts by meta key and value:

Read More
DELETE wp_posts FROM wp_posts
INNER JOIN wp_postmeta ON wp_postmeta.post_id = wp_posts.ID
WHERE (wp_postmeta.meta_key = 'vimport_key' AND wp_postmeta.meta_value = 'some value');

This Query deletes rows from wp_postmeta which don’t have a match with a post. 

DELETE pm
FROM wp_postmeta pm
LEFT JOIN wp_posts wp ON wp.ID = pm.post_id
WHERE wp.ID IS NULL
Post Views: 2

Related posts

Leave a Reply

1 comment

  1. Either look at the cascade option or do this…

    DELETE p, pm
  FROM wp_posts p
 INNER 
  JOIN wp_postmeta pm
    ON pm.post_id = p.ID
 WHERE pm.meta_key = 'vimport_key' 
   AND pm.meta_value = 'some value';

    Note that, by convention, we normally write this the other way round, such that the WHERE clause references the 1st table, but it’s logically (and performatively) identical – just in the same that a * b is the same as b * a.