WordPress.org

Ready to get started?Download WordPress

Forums

SQL Query to delete all tags (26 posts)

  1. unseenmortal
    Member
    Posted 4 years ago #

    I want all my tags to be deleted, are there any sql queries for this? Thanks

  2. Mark / t31os
    Moderator
    Posted 4 years ago #

    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.

  3. unseenmortal
    Member
    Posted 4 years ago #

    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..

  4. Mark / t31os
    Moderator
    Posted 4 years ago #

    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)..

  5. unseenmortal
    Member
    Posted 4 years ago #

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

  6. Mark / t31os
    Moderator
    Posted 4 years ago #

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

    Old article but good examples...

  7. unseenmortal
    Member
    Posted 4 years ago #

    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...

  8. peps974
    Member
    Posted 4 years ago #

    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
  9. Mark / t31os
    Moderator
    Posted 4 years ago #

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

  10. mickscool
    Member
    Posted 4 years ago #

    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?

  11. mickscool
    Member
    Posted 4 years ago #

    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?

  12. MichaelH
    Member
    Posted 4 years ago #

    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.

  13. Mark / t31os
    Moderator
    Posted 4 years ago #

    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... :)

  14. mickscool
    Member
    Posted 4 years ago #

    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 !!!

  15. MichaelH
    Member
    Posted 4 years ago #

    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.

  16. mickscool
    Member
    Posted 4 years ago #

    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 :)

  17. bekar09
    Member
    Posted 4 years ago #

    t31os_'s query has screwed my categories. all my categories are deleted. i am using WP 2.8.6. Please use with care.

  18. Mark / t31os
    Moderator
    Posted 4 years ago #

    Which is why Michael replied suggesting that any attempt to use such a query should be preceded with a backup of your database. I can't test for all cases.

    If you really need to delete all the tags, then you can look at how WordPress does it, if i remember correctly it's a loop (query per tag). Alternatively there's the option of soliciting a professsional to write a query for you.

    Sorry the query caused an issue, the intention was to help, and my interest was in resolving a problem not making one, but i would say (although i didn't previously state), complex queries should always be following a backup of important data (though i'm sure i don't need to point out the importance of backups here anyway).

  19. bekar09
    Member
    Posted 4 years ago #

    hi t31os_,
    my intention was not to hurt you. we all know that we try to help each other. I just warned people that it blew my database.

  20. Mark / t31os
    Moderator
    Posted 4 years ago #

    Hi, no problem bekar.. ;)

    Simply pointing out i do care if my suggestions cause a problem for someone, but all the same i can't (and won't) take responsibility for any users inability to backup data before performing queries like the above.

    Not meaning to bite at all, just making my stance clear in regards to code causing problems.

  21. Nagmay
    Member
    Posted 4 years ago #

    MichaelH had the right idea with: "Then use the Bulk Delete mechanism to delete 999 tags at a time."

    It's safe and efficient.

    ... But I, like others on this tread, want to only delete the tags with low post counts. Unfortunatly, you can't select "sort by count" on the tag admin page. So here's how to temporarily hack that functionality:

    1) Open /wp_admin/template.php

    2) Go to line 721 :

    $args = array('offset' => $start, 'number' => $pagesize, 'hide_empty' => 0);

    3) Temporarily change it to:

    $args = array('offset' => $start, 'number' => $pagesize, 'hide_empty' => 0, 'orderby' => 'count');

    This will cause the tag list to sort by count for your bulk deletion needs.

    Make sure to change it back when you are done.

  22. bekar09
    Member
    Posted 4 years ago #

    but i cannot delete 999 tags at a time. it says "Request URI too large....."

    what should i configure with my host to increase the request uri?

  23. Mark / t31os
    Moderator
    Posted 4 years ago #

    The problem with using the in-built functionality with or without gabrielmcgovern's change above is that you'll essentially be performing 1 query per tag, which is far more intensive for any server to process then a single query that deals with the lot (delete 1000 tags = 1000 queries).

    I think that's why you end up with a "Request URI too large", because you're asking too much from the server.. i could be wrong of course..

  24. Mark / t31os
    Moderator
    Posted 4 years ago #

    I think i realise the problem that may have occured before, i believe the delete line should actually be... (i think)..

    DELETE a,c

    Which leaves the JOIN to the relationships table(b alias) as optional in the query.

    EXPLAIN command is quite helpful figuring out queries, though you can't use it on DELETE queries (*sigh).. For the most part deletes are select queries (in my mind), but with minor differences... it's awkward to test these queries without removing data (it's not fun adding and removing post tags over and over, and over... lol)..

    I'd welcome anybody else's input regarding delete queries .. if not only to confirm or deny what i've said above..

  25. nemonet
    Member
    Posted 4 years ago #

    This worked great for me, thank you!!

    Do you have a blog/paypal, i can buy you a coffee or a beer?

    Aloha...

  26. priyaank
    Member
    Posted 4 years ago #

    Thanks t31os_

    This worked great for me, thank you!!

Topic Closed

This topic has been closed to new replies.

About this Topic