Mass Delete Tags

Why not just use the Administration > Posts > Tags and do a Bulk delete?

But just in case you don’t like that idea ??

See https://www.ads-software.com/support/topic/311665?replies=13#post-1248204 for an example of what the SQL could look like.

Hi MichaelH,

I was using an autoposter which has accumulated around 18k tags. i think if i try to delete through Administration > Posts > Tags then it will take me ages.

I have even tried t31os_’s delete query and it blew my database. If you look at the thread, i already posted my comments there.

can you refine the query submitted by t31os_ ?

thanks.

i think if i try to delete through Administration > Posts > Tags then it will take me ages

Hmm at 999 tags per page, that’s about 18, click the check box to check all tags, select the Bulk action of Delete, click the Apply button.

thanks MichaelH,
let me try this out. btw does Simple Tags plugin auto-tag posts which have already been published? if so, then how does it do that?

That would be a question for a new topic.

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?

If you don’t want to use the admin interface then you’re only left with a query…

If you have phpmyadmin, open that up, select the WordPress database, and click the SQL tab at the top..

Plonk this into the box.

EXPLAIN SELECT t.*,tt.*,tr.*
FROM wp_terms AS t
LEFT JOIN wp_term_taxonomy as tt ON tt.term_id = t.term_id
LEFT JOIN wp_term_relationships tr ON tr.term_taxonomy_id = tt.term_taxonomy_id
WHERE tt.taxonomy = 'post_tag'

..then click “Go”..
That will explain the query for you (if that helps at all).
..or..

SELECT t.*,tt.*,tr.*
FROM wp_terms AS t
LEFT JOIN wp_term_taxonomy as tt ON tt.term_id = t.term_id
LEFT JOIN wp_term_relationships tr ON tr.term_taxonomy_id = tt.term_taxonomy_id
WHERE tt.taxonomy = 'post_tag'

Will just run a select query.

..or..

DELETE t.*,tt.*,tr.*
FROM wp_terms AS t
LEFT JOIN wp_term_taxonomy as tt ON tt.term_id = t.term_id
LEFT JOIN wp_term_relationships tr ON tr.term_taxonomy_id = tt.term_taxonomy_id
WHERE tt.taxonomy = 'post_tag'

Will delete all relevant entries for post tags in each necessary table.
NOTE: If you consider running this, run the select/explain queries prior so you can see what is being selected first.

NOTE: Backup before running.
FURTHER NOTE: The delete query will return a number to indicate the effected rows, ..!! this means the total rows (across all 3 tables).. so will equate to more then the total amount of tags.. ( for example deleting 10 tags maybe effect 18 or 20 rows total ).

I’ve run the above query in phpmyadmin 5 times over and yet to get bad results..

You’ll notice the queries are essentially one and the same, with exception to selecting or removing data. Anyway, hope that helps…

ADDITIONAL: Just wanted to also note the delete query suggested here, much like (if not the same) as posted in the other tag related thread has been tested on wp 2.8, through to 2.9 beta2 … However i very much doubt this is the problem, if the query fails, if anything i think it would be a mysql version issue (my syntax could be off for older versions – which is why it’s recommended to run it as a select query first, so you can see the returned/matched rows).

Hi t31os_,

this query looked perfect. but it again disturbed my database. I lost some of my categories. I can see a weird behaviour.

in the admin dashboard, I can see 9 categories, but when i go to Posts->Categories, i can see only 4 categories. and the most amazing thing is when i run this query(note tt.taxonomy = ‘category’) i can see the categories which existed before running the delete statement. if it is there in the database, then why is it not shown in the admin panel of WordPress?

SELECT t . * , tt . * , tr . *
FROM wp_terms AS t
LEFT JOIN wp_term_taxonomy AS tt ON tt.term_id = t.term_id
LEFT JOIN wp_term_relationships tr ON tr.term_taxonomy_id = tt.term_taxonomy_id
WHERE tt.taxonomy =  'category'
LIMIT 11190 , 30

can you help me out?

I’ve also seen some problems, but that’s only been following my own fiddling in the terms and taxonomies tables (stupidly removed the default category once before, this caused problems with categories in itself).

The dashboard widget seems to count the matched terms in the database, which is not necessarily indicative of how many show on say the categories page (mine was +1, 6 on my category page, 7 according to the dashboard) ..

Checking the database revealed some left over data from an old category i deleted previously (in my meddling), once that was removed the count on the dashboard corrected itself.. There were some references in the relationships table and 1 in the terms table, but none in the taxonomy table (i don’t know how i managed that).

If you’re fiddling with queries, i only hope you’re doing this on local test install.

I wrote this query today..

SELECT t.*,tt.*,tr.*
FROM wp_terms AS t
INNER JOIN wp_term_taxonomy as tt
INNER JOIN wp_term_relationships as tr
WHERE tt.term_id = t.term_id
AND tr.term_taxonomy_id = tt.term_taxonomy_id
AND tt.taxonomy = 'post_tag'

Which is inline with the syntax in the docs here.
https://dev.mysql.com/doc/refman/5.1/en/delete.html

Change the SELECT for a DELETE and it’s a delete query.

Of course do note, i’m testing under and writing for MySQL 5..

I even flushed all my post tags, and categories, created 4 or 5 test ones (categories and tags), assigned posts to each, monitored the entries appearing in the database via phpmyadmin, run some test delete queries, nothing that shouldn’t be removed was.

If you experience different behaviour, create a test database, import your wordpress term tables, (terms, term_relationships, taxonomys), remove all the entries from the newly created tables, create some new ones, say 4-5 of each, then run the delete queries on the test tables..

See what your results are..

Hi Guys,

to save you all the SQL trouble: https://www.ads-software.com/extend/plugins/mass-delete-tags/