[wp-hackers] Cleaning up tags

[Mike Schinkel]

Hi Lari,

I believe what you need is this (be sure to back up first, just in case):

DELETE FROM 
  wp_term_relationships 
WHERE 
  term_taxonomy_id IN (
    SELECT 
      tt.term_taxonomy_id
    FROM 
      wp_terms t 
      INNER JOIN wp_term_taxonomy tt ON t.term_id = tt.term_id
    WHERE 
      t.slug IN ( 'foo', 'bar', 'baz' )
  )

You should run this first to eyeball the records you plan to delete:

SELECT * FROM 
  wp_term_relationships 
WHERE 
  term_taxonomy_id IN (
    SELECT 
      tt.term_taxonomy_id
    FROM 
      wp_terms t 
      INNER JOIN wp_term_taxonomy tt ON t.term_id = tt.term_id
    WHERE 
      t.slug IN ( 'foo', 'bar', 'baz' )
  )

It presumes your terms to remove from the wp_term_relationships table are 'foo', 'bar', and 'baz.'  It also assumes you've not deleted them from the wp_terms table yet nor from the wp_term_taxonomy table yet.

If you've deleted from the wp_term_taxonomy table already you'll need to run this instead (back up your database first):

DELETE FROM 
  wp_term_relationships 
WHERE 
  term_taxonomy_id NOT IN (
    SELECT 
      tt.term_taxonomy_id
    FROM 
      wp_term_taxonomy
  )

And of course eyeball with this before deleting:

SELECT * FROM 
  wp_term_relationships 
WHERE 
  term_taxonomy_id NOT IN (
    SELECT 
      tt.term_taxonomy_id
    FROM 
      wp_term_taxonomy
  )

Hope this helps.

-Mike

On Jan 8, 2010, at 8:43 AM, Lari Elovainio wrote:

> 
> On 8. 1. 2010, at 14.00, wp-hackers-request at lists.automattic.com wrote:
> 
> Thank you Eric and Bruce for your help. I might have been a bit unclear, though, on what I wanted. The tags I’d like to delete are only used on one post each. There are over 8000 of these tags. I know how to delete these from the wp_term_taxonomy table.
> 
> What I don’t know, is how to delete the corresponding 8000 rows from the wp_term_relationships table. I guess it requires some kind of join, but i’m not that familiar with mysql. So I need to delete rows from wp_term_relationships where 'term_taxonomy_id' is something that has 'count' lower than 2 in wp_term_taxonomy. How do I do that? I’m sorry if this is trivial but I haven’t been able to figure it out.
> 
> Removing these tags might lower the visitor count a bit, but these tags in question are very low quality, so it’ll probably lower bounce rate, too. For instance, there are lots of tags that are song names on articles about new album releases. I’m trying to educate the client on how to tag but there are many writers and they are slow learners.
> 
> Thank you,
> Lari
> _______________________________________________
> wp-hackers mailing list
> wp-hackers at lists.automattic.com
> http://lists.automattic.com/mailman/listinfo/wp-hackers