How to delete ALL comments from certain category in Wordpress database?

Question

I'm trying to delete all comments from posts in a certain category in a Wordpress database.

The Wordpress database is rather complicated so this must be probably some join of wp_posts, wp_comments and wp_term_relationships as the categories are not stored with neither posts nor comments.

How can I do this? I'm struggling to come up with a query.

Answer 1

I can provide you the delete command from join tables. Then you need to add the relevant tables and columns though

-- using LEFT JOIN

DELETE  x.*
FROM    table1 AS x 
LEFT JOIN
        table2 AS y 
ON      y.id = x.id

-- using IN:

DELETE  
FROM    table1 AS x
WHERE   id IN
        (
        SELECT  id
        FROM    table2
        )

Usually left join is faster than in. Looking at that ERD, you would also better watch out for any foreign key relationships if delete on cascade is not implemented.

Answer 2

The wordpress database seems fairly straight forward and is extremely well documented:

http://codex.wordpress.org/Database_Description

It seems to me that all the category information is stored in the table wp_term_relationships. So you should select from the comments table, join it with the posts table and then the term_relationships table in order to get the category for each comment.

Something like:

SELECT *
FROM wp_comments
WHERE comment_post_ID
IN (

SELECT c.object_id
FROM wp_terms a, wp_term_taxonomy b, wp_term_relationships c
WHERE a.name = 'my_category_slug'
AND b.term_id = a.term_id
AND b.taxonomy = 'category'
AND c.term_taxonomy_id = b.term_taxonomy_id
)