Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

This website lists a nice way of deleting posts based on category

http://www.tripwiremagazine.com/2010/06/how-to-effectively-bulk-delete-many-posts-in-wordpress.html

The relevant code is:

delete a,b,c,d
FROM wp_posts a
LEFT JOIN wp_term_relationships b ON ( a.ID = b.object_id )
LEFT JOIN wp_postmeta c ON ( a.ID = c.post_id )
LEFT JOIN wp_term_taxonomy d ON ( d.term_taxonomy_id = b.term_taxonomy_id )
LEFT JOIN wp_terms e ON ( e.term_id = d.term_id )
WHERE e.term_id =<category id>

where <category id> needs to be replaced with the relevant number.

I need to modify this code to mass delete custom post types based on their taxonomy.

By pressing on the edit button in wordpress admin i can retrieve the following information:

http://www.domain.com/wp-admin/edit-tags.php?action=edit&taxonomy=estate_agents&tag_ID=9098&post_type=os_estate

So i will need to modify the SQL code above to take:

post_type= os_estate
taxonomy= estate_agents
tag_ID= 9098

Any help with this would be much appreciated.

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

Trust you have backed up your wordpress database. In any case, as the article suggests, worth trying with a select statement first to see which posts are returned:

SELECT DISTINCT ID, post_title, post_type, post_status, d.taxonomy, e.name
FROM wp_posts a
LEFT JOIN wp_term_relationships b ON ( a.ID = b.object_id )
LEFT JOIN wp_postmeta c ON ( a.ID = c.post_id )
LEFT JOIN wp_term_taxonomy d ON ( d.term_taxonomy_id = b.term_taxonomy_id )
LEFT JOIN wp_terms e ON ( e.term_id = d.term_id )
WHERE d.taxonomy = 'estate_agents' AND a.post_type = 'os_estate' AND e.term_id = 9098

Then, if you're happy:

DELETE a,b,c
FROM wp_posts a
LEFT JOIN wp_term_relationships b ON ( a.ID = b.object_id )
LEFT JOIN wp_postmeta c ON ( a.ID = c.post_id )
LEFT JOIN wp_term_taxonomy d ON ( d.term_taxonomy_id = b.term_taxonomy_id )
LEFT JOIN wp_terms e ON ( e.term_id = d.term_id )
WHERE d.taxonomy = 'estate_agents' AND a.post_type = 'os_estate' AND e.term_id = 9098

Specifying DELETE a,b,c means that only rows in the wp_posts, wp_term_relationships and wp_postmeta tables will be deleted; rows in wp_term_taxonomy (d) and wp_terms (e) will not.

share|improve this answer
    
thank you Jon, will try this out shortly! What if i want to include multiple term_ID= 9098, 9099 and so on... how would i modify your code? "All term_id" are included within taxonomy "estate_agents" –  Mario Lorenzo Jul 16 '13 at 14:12
1  
As for the comment: Make use of the MySQL IN comparison operator. WHERE d.taxonomy = 'estate_agents' AND a.post_type = 'os_estate' AND e.term_id IN ( 9098, 9099, 9100 ) –  Johannes Pille Jul 16 '13 at 14:21
1  
@Jon ok there is a problem with the code - it also deletes the taxonomy name - there are other posts inside that taxonomy and hence I wouldn't like the taxonomy name to be deleted... –  Mario Lorenzo Jul 16 '13 at 21:39
    
@Mario yes you're right. Removing ,d (the alias for the taxonomy table) from the delete statement will fix this. I have updated the answer –  Jon Jul 18 '13 at 4:28
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.