Support » Fixing WordPress » SQL Query to delete all tags

Viewing 15 replies - 1 through 15 (of 25 total)
  • Go to the post tags page in the admin, check the checkbox at the top of the tag list, then use the Bulk Action delete…

    A query would require joins to ensure all the relational data is removed.

    Far safer to use the post tag page in the admin so all the relational data is removed correctly.

    Request-URI Too Large
    The requested URL’s length exceeds the capacity limit for this server.

    I get this error, i have 17,000+ tags, if i’ll remove them via wp-admin, it’ll take me weeks..

    You’ll need a custom written delete query with joins.

    Delete joins are not something i’m good at it, sorry.. (i did have an attempt in Phpmyadmin, but failed badly)..

    I tried searching in google but haven’t really found any sql queries to do it.. Hope someone could help me out..

    Ah, found some good examples..
    electrictoolbox.com/article/mysql/cross-table-delete/

    Old article but good examples…

    Thanks for the link but I didn’t understand really. I just need a working SQL Query so I could run in on phpMyAdmin.. Thanks anyway.. Still hoping someone will give me an answer…

    Hey guys,
    I have found that in another post but I tried to run it and it doesn’t seem to end. ANyone has found a nice, sleek mysql query for that purpose (bulk delete of unused tags):

    SELECT * FROM wp_terms wt
    INNER JOIN wp_term_taxonomy wtt ON wt.term_id=wtt.term_id
    INNER JOIN wp_term_relationships wtr ON wtr.term_taxonomy_id=wtt.term_taxonomy_id
    LEFT JOIN wp_posts wp ON wp.ID=wtr.object_id
    WHERE
    taxonomy='post_tag'
    AND ID IS NULL
    AND NOT EXISTS(SELECT * FROM wp_terms wt2
                    INNER JOIN wp_term_taxonomy wtt2 ON wt2.term_id=wtt2.term_id WHERE wtt2.parent=wt.term_id)
    
    ORDER BY name

    That’s a select query, so i very much doubt it’ll remove anything.

    Hi guys,
    I am also looking for something similar but cannot find anything. I have around 1800 tags in my site that i would like to delete. The admin page takes 10 minutes to delete 1 tag, so it wud take me weeks to delete 1800 tags.

    anyone?

    I really need an answer to my question. I cannot delete or add any new tags to the system and my site traffic has dropped like crazy..

    anyone?

    Visit your admin Posts->Posts Tags. In screen options, change the number of tags to 999. Then use the Bulk Delete mechanism to delete 999 tags at a time.

    Managed to figure out how to write a delete join. Removes all post tags that have a 0 count.

    As WordPress Query: [updated as per #]

    <?php
    $wpdb->query("
    DELETE a,c
    FROM
    	$wpdb->terms AS a
    	LEFT JOIN $wpdb->term_taxonomy AS c ON a.term_id = c.term_id
    	LEFT JOIN $wpdb->term_relationships AS b ON b.term_taxonomy_id = c.term_taxonomy_id
    WHERE (
    	c.taxonomy = 'post_tag' AND
    	c.count = 0
    	)
    ");
    ?>

    Mysql Query (for use in PHPMYADMIN):

    DELETE a,c
    FROM
    	database.prefix_terms AS a
    	LEFT JOIN database.prefix_term_taxonomy AS c ON a.term_id = c.term_id
    	LEFT JOIN database.prefix_term_relationships AS b ON b.term_taxonomy_id = c.term_taxonomy_id
    WHERE (
    	c.taxonomy = 'post_tag' AND
    	c.count = 0
    	)

    Adjusting “database” and “prefix_” to your WordPress database name and WordPress table prefix (assuming they’re not just default).

    I’ve tested in both WordPress and PHPMYADMIN, using MySQL 5.1 and PHP5.

    Maybe a little bit late, but it may be of help to someone… 🙂

    Thanks a lot t31os_

    It’s never late 🙂 I’ll give it a try this evening. So if I want to remove all the post_tags, I guess I just have to remove this from the php file:
    “AND
    c.count = 0

    rite? I hope that won’t mess up my categories or will it?

    Thanks again !!!

    I hope that won’t mess up my categories or will it?

    That’s what this does–tags

    c.taxonomy = 'post_tag'

    But, you should be backing up your database before any operation such as this so that you don’t have to worry if it messes up something else. See WordPress Backups.

    OK, I tried and it worked like a charm but now I am having a small issue adding new tags.

    I have added some new tags but when i click ‘post tags’ no tags show up under ‘popular tags’.

    Moreover, when I try to associate any tags to a post, it throws an error saying duplicate tag entry. So, what’s the correct way to associate tags to the posts??

    Thanks a zillion again for that auto tag delete query 🙂

Viewing 15 replies - 1 through 15 (of 25 total)
  • The topic ‘SQL Query to delete all tags’ is closed to new replies.