comments – AgileAdam.com

Please TEST these queries before running them on a live site! I do not want to be held responsible if something goes wrong.

Here is how a colleague explained his situation (the solution follows):

The trap is that if you create a content type and don’t remember to set comments off, all the nodes you create have to be updated manually to turn it off, even after you change the default in the content type settings. In our case, I imported a ton of records, and only later realized comments were on.

Part 1: Turn off commenting on all nodes of a particular content type (we’ll use ‘job’ as the content type in the examples below).

UPDATE node SET comment = 0 WHERE type = 'job'

1	UPDATE node SET comment = 0 WHERE type = 'job'

Now, don’t forget to disable commenting on the content type itself (so that nodes (of this content type) created from this point forward do not allow comments).

Part 2: Delete all comments left on nodes of a particular content type. First, take a look at what comments you’ll be deleting… just to be sure of what you’re getting into.

SELECT comments.cid, comments.nid, node.type
FROM comments INNER JOIN node ON comments.nid = node.nid
WHERE comments.nid = 'job'

SELECT comments.cid, comments.nid, node.type

FROM comments INNER JOIN node ON comments.nid = node.nid

WHERE comments.nid = 'job'

Now, let’s delete those comments:

DELETE FROM comments USING node INNER JOIN comments
WHERE comments.nid = node.nid AND node.type = 'job'

1 2	DELETE FROM comments USING node INNER JOIN comments WHERE comments.nid = node.nid AND node.type = 'job'