0

Trying figure out how I can use some SQL/$wpdb->query to delete all the terms from a taxonomy that are related to specific post IDs. The following works beautifully to delete all the terms from a specific taxonomy, but I need to modify it to only remove ones associated with specific post IDs as mentioned.

// Clear Terms from Taxonomy 'post_tag'
DELETE t, tr, tt
FROM wp_terms t  
INNER JOIN wp_term_taxonomy tt ON t.term_id = tt.term_id
INNER JOIN wp_term_relationships tr ON tt.term_taxonomy_id = tr.term_taxonomy_id
WHERE tt.taxonomy = 'post_tag'

I'm also using the following to delete posts (works great):

// Delete Posts 
$wpdb->query( "DELETE FROM $wpdb->posts WHERE ID IN (".implode( ", ", $postIds ).")" );

I've tried to do the following but it doesn't clear the associated terms they just remain as orphaned in the DB, it just clears the relationship.

// Delete Post Terms
$wpdb->query( "DELETE FROM wp_term_relationships WHERE object_id IN (".implode( ", ", $postIds ).")" );

I'm a novice when it comes to SQL, any help is appreciated, thanks in advance!

1

So if you're sure you want to delete the actual term as well as the relationship that assigns the term to the post, this will do it (tested and works for me):

delete term from wpterm_taxonomy term, wpterm_relationships rel 
where  term.term_taxonomy_id = rel.term_taxonomy_id and rel.object_id IN (1) ;

And obviously replace the '1' there with how you're selecting the post ID's

I would strongly recommend you test this before running it with e.g. this query which will show which terms will be deleted before you do it! And take a database backup before you're doing large deletes in case something goes wrong ;-)

select term.term_taxonomy_id from wpterm_taxonomy term, wpterm_relationships rel 
where  term.term_taxonomy_id = rel.term_taxonomy_id and rel.object_id IN (1) ;

Again replace '1' with however you create your list of post ID's

| improve this answer | |
  • Thanks for the reply! Tested, but I changed it slightly into a single line as share.getcloudapp.com/RBuqZ9Pm and the terms still remain? Assuming the syntax is wrong? Did notice the missing underscores after "wp" so I changed those (how my DB prefix is setup). – contempoinc Jul 1 at 17:28
  • you could do the select before to see what it's doing to do and may not be obvious but if you delete the rows from the relationship table first, then it won't work because that's how this query figures out which terms to delete based on the post ID. Your edit of it looks fine – mozboz Jul 1 at 18:36
  • Ah duh, that makes total sense lol — removed the share.getcloudapp.com/6qu2Grg6 and it works beautifully, thanks so much! – contempoinc Jul 1 at 18:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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