• I have 1000’s of tags and I would like to simply delete all tags that aren’t used more than X times… i.e. 5 times.

    Does anyone know of a simple way to do this? Even straight SQL would totally ROCK!

    Thanks a lot ??
    Mitch

Viewing 9 replies - 1 through 9 (of 9 total)
  • First, BACK UP YOUR DATABASE!!

    Since you cannot delete from a table and also use that table in a subquery, you must create a new table having the term_taxonomy_id’s that need to be deleted:

    [Code Removed as it’ll wipe your categories! See later posts.]

    Now, you can use temprel in the subquery of a DELETE:

    DELETE FROM wp_term_relationships
    WHERE term_taxonomy_id IN
      (SELECT term_taxonomy_id FROM temprel)
    Thread Starter mitch247

    (@mitch247)

    Thanks a lot for your response! What do you think of this code, will it work?

    $x = 5; // set this to any number
    $sql = "SELECT <code>name</code> FROM <code>wp_terms</code>";
    $result = mysql_query($sql);
    $count = array();
    while($row = mysql_fetch_assoc($result))
    {
      $count[$name]++;
    }
    foreach($count as $key = $value)
    {
      if($value < $x)
        {
          $sql2 = "DELETE FROM <code>wp_terms</code> WHERE <code>name</code> = '". $key ."'";
          $result2 = mysql_query($sql2);
        }
    }

    MY CODE IS INCORRECT! DO NOT USE IT!! IT WILL DELETE CATEGORIES AND LINKS AS WELL AS TERMS!!

    Your code is only deleting the wp_terms entries. If you delete from that table, you will leave ‘orphan’ records. Also, if there happens to be a category with the same name as a tag, it will be deleted. Finally, each tag occurs in the terms table only once, so the count will always be 1.

    The code I gave deletes the wp_term_relationships and leaves no orphans.

    Actually, you need to delete from 3 tables:

    1. wp_term_relationships
    2. wp_term_taxonomy
    3. wp_terms

    If you delete the term_relationships, the tags will no longer show up on any posts. Deleting from the other tables is a little more complex than from term_relationships. I ignored those tables because once the relationships are gone, the tags are ‘inactive’ so to speak.

    Thread Starter mitch247

    (@mitch247)

    So I should do this:

    CREATE TABLE temprel SELECT term_taxonomy_id, count(1) as postcount
    FROM <code>bmc_term_relationships</code>
    GROUP BY term_taxonomy_id
    HAVING count(1) < 5

    and then:

    DELETE FROM wp_term_relationships
    WHERE term_taxonomy_id IN
      (SELECT term_taxonomy_id FROM temprel)
    DELETE FROM wp_term_taxonomy
    WHERE term_taxonomy_id IN
      (SELECT term_taxonomy_id FROM temprel)
    DELETE FROM wp_terms
    WHERE term_taxonomy_id IN
      (SELECT term_taxonomy_id FROM temprel)

    DO NOT USE THE FIRST SQL CREATE TABLE STATEMENT I POSTED!!! IT WILL DELETE CATEGORIES AND LINKS AS WELL AS TAGS!!

    Use this for creating the temprel table:

    CREATE TABLE temprel
    SELECT tr.term_taxonomy_id, count(1) AS postcount
    FROM wp_term_relationships tr
    JOIN wp_term_taxonomy tt ON (tr.term_taxonomy_id = tt.term_taxonomy_id)
    WHERE tt.taxonomy = 'post_tag'
    GROUP BY tr.term_taxonomy_id
    HAVING postcount < 5

    You could use the sql statements I gave in a wp get-results() call.

    Thread Starter mitch247

    (@mitch247)

    vtxyzzy… Thanks a lot for that updated code… I ran the following and still show thousands of tags in my dashboard.

    CREATE TABLE temprel
    SELECT tr.term_taxonomy_id, count(1) AS postcount
    FROM wp_term_relationships tr
    JOIN wp_term_taxonomy tt ON (tr.term_taxonomy_id = tt.term_taxonomy_id)
    WHERE tt.taxonomy = 'post_tag'
    GROUP BY tr.term_taxonomy_id
    HAVING postcount < 5

    and

    DELETE FROM wp_term_relationships
    WHERE term_taxonomy_id IN
      (SELECT term_taxonomy_id FROM temprel)

    It seems that that did work at the post level. However I still see thousands in admin. How can I use that new temporary table to clear out the tags in admin as well?
    Thanks again for your help!

    As I said before, tags are defined in 3 tables. You have cleared the first one – there are two left: wp_term_taxonomy and wp_terms.

    You can delete the rows in wp_term_taxonomy with this:

    DELETE FROM wp_term_taxonomy
    WHERE term_taxonomy_id IN
      (SELECT term_taxonomy_id FROM temprel)

    Then, delete from wp_terms with this:

    DELETE FROM wp_terms
    WHERE term_id NOT IN
       (SELECT term_id FROM wp_term_taxonomy)
    Thread Starter mitch247

    (@mitch247)

    OK I did run it successfully… I moved to my next domain and tried to do the same thing. However I get this error when I try to create that temp table:

    import.php: Missing parameter: import_type (FAQ 2.8)
    import.php: Missing parameter: format (FAQ 2.8)

    Have you ever seen this?

    Sorry, I have not seen that before.

Viewing 9 replies - 1 through 9 (of 9 total)
  • The topic ‘Mass delete unpopular Tags’ is closed to new replies.