0

I've realized the Wordpress Tag Cloud has no weighting for newer posts. I have thousands of old posts that I want to no longer be counted for tagging. So, what I want to do is remove all tags from older, archived posts. All of these older posts have a category of Archive and a category_ID of 705. I need help constructing the Mysql query to remove all tags from posts with the category_id of 705. - G Arnold

1

Well, think splitting the task in multiple pieces. First we need to get the post id's which has the category with id 705 and then we'll remove the tags from those posts.

Now if we transform this idea in to SQL then the query will be-

DELETE wp_term_relationships FROM wp_term_relationships
INNER JOIN wp_term_taxonomy ON ( wp_term_taxonomy.term_taxonomy_id = wp_term_relationships.term_taxonomy_id )
WHERE wp_term_taxonomy.taxonomy = 'post_tag' AND wp_term_relationships.object_id IN (SELECT id
FROM (SELECT wp_posts.id FROM wp_posts
       INNER JOIN wp_term_relationships
               ON wp_term_relationships.object_id = wp_posts.id
       INNER JOIN wp_term_taxonomy
               ON wp_term_taxonomy.term_taxonomy_id = wp_term_relationships.term_taxonomy_id
       INNER JOIN wp_terms
               ON wp_term_taxonomy.term_id = wp_terms.term_id
WHERE wp_terms.term_id = 705 AND wp_term_taxonomy.taxonomy = 'category' ) AS posts)

This is tested. Don't forget to backup the DB first. Anyway, the table prefix(wp_) may vary based on settings. So please check the table prefix before running the query.

Hope that helps.

  • Your query above worked perfectly, many thanks, Dramatist! - G. Arnold – user3874046 Jul 5 '17 at 17:18
  • One more thing, though, the query above did delete all tags from the category of posts I needed, but I noticed that the Tag Count was not updated. So I ran this query to update the counts, and everything seems good now: stackoverflow.com/questions/18669256/… – user3874046 Jul 5 '17 at 17:26
  • If this answer helped you then please accept the answer by checking the check sign left side of the answer. That would be very generous ! @user3874046 – CodeMascot Jul 6 '17 at 1:37
1

To select all terms IDs that need to be deleted:

select wp_terms.term_id from wp_terms
left join wp_term_taxonomy on wp_terms.term_id = wp_term_taxonomy.term_id
where wp_terms.term_id in (
    select term_taxonomy_id from wp_term_relationships where object_id in (
        select object_id from wp_term_relationships where term_taxonomy_id = 705
    )
)
and wp_term_taxonomy.taxonomy = 'post_tag'

Don't forget to create first a backup of your database.

Basically, first I get all posts IDs that have category ID 705 (select object_id from wp_term_relationships where term_taxonomy_id = 705), then I use those IDs to get all terms IDs that were added to those posts on wp_term_relationships table (select term_taxonomy_id from wp_term_relationships where object_id in ...) and finally I get all IDs that matter from wp_terms by filtering only those that tags (and wp_term_taxonomy.taxonomy = 'post_tag').

After you get all these IDs you can use them to delete with a SQL query like the_dramatist answer, or you can use Wordpress functions so that WP takes care of everything needed to have a clean database:

<?php
$ids = array(12, 23, 45, 100);
foreach($ids as $id) {
    wp_delete_term( $id, 'post_tag' );
}
?>
  • Would you please describe the query a little bit ? Cause as far I see the query doesn't delete any thing and without deleting you can't remove tags form database. @dbeja – CodeMascot Jul 4 '17 at 12:06
  • yes, I just created the sql query to get the list of IDs of terms to be deleted. I edited the answer to explain the query and what you can do with those IDs. Hope it helps. – dbeja Jul 4 '17 at 13:19
  • Well, I appreciate your effort, but this is bad practice. I don't know if your code will work or not, but for this kinda one time task you should not use WP functions. Full SQL is better. And beside he/she has mentioned SQL in his/her question. – CodeMascot Jul 4 '17 at 13:33
  • yes, I was just giving more options of what could be done with the list of IDs. my code was tested and my suggestion of WP functions was just so that you don't need to worry with all tables relations, but I agree your answer is the right one, that's why I mentioned it on my answer. thanks for reviewing it. – dbeja Jul 4 '17 at 13:53

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.