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).MySQL1UPDATE 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.MySQL123SELECT comments.cid, comments.nid, node.typeFROM comments INNER JOIN node ON comments.nid = node.nidWHERE comments.nid = 'job'MySQL12DELETE FROM comments USING node INNER JOIN commentsWHERE comments.nid = node.nid AND node.type = 'job'