Remove duplicated comments in Wordpress?

Question

Anyone know sql query or wordpress plugin which may help me to remove duplicate comments.

While I was importing posts, comments into wordpress, i got some timeouts, and repeated process, so some of comments posted twice.

score 6 · Accepted Answer · answered Oct 26 '13 at 23:46

Taking a look at some of the images of WordPress' schema then you should be able to identify the records you want to delete with a query such as

SELECT wp_comments.*
FROM wp_comments
LEFT JOIN (
    SELECT MIN(comment_id) comment_id
    FROM wp_comments
    GROUP BY comment_post_id, comment_author, comment_content
) to_keep ON wp_comments.comment_id = to_keep.comment_id
WHERE to_keep.comment_id IS NULL

You should run the query above and make sure you it is returning the correct records (the ones that will be deleted). Once you are satisfied the query is working then simply change it from a SELECT to a DELETE

DELETE wp_comments
FROM wp_comments
LEFT JOIN (
    SELECT MIN(comment_id) comment_id
    FROM wp_comments
    GROUP BY comment_post_id, comment_author, comment_content
) to_keep ON wp_comments.comment_id = to_keep.comment_id
WHERE to_keep.comment_id IS NULL

Thanks. Gonna try it later, I encountered bigger problem right now. — Mezelderz, Oct 27 '13 at 05:41
This is roughly correct, but note that many plugins (including EDD) will store data in the `wp_comments` table. To prevent these being removed I recommend adding `comment_type = 'comment'` to the end of the above queries. This will limit the deletion to true comments and leave other psuedo-comments alone. — Andrew, Oct 11 '20 at 23:11

score 1 · Answer 2 · answered Feb 06 '19 at 14:42

Wow, this worked like a charm, a more aggressive form I used eventually to eliminate all duplicate comments regardless of author or post ID will be :

DELETE wp_comments
FROM wp_comments
LEFT JOIN (
    SELECT MIN(comment_id) comment_id
    FROM wp_comments
    GROUP BY comment_content
) to_keep ON wp_comments.comment_id = to_keep.comment_id
WHERE to_keep.comment_id IS NULL

This will delete useless short comments that work like a template like: "Thanks", "Great"....

score 0 · Answer 3 · answered Dec 16 '13 at 04:01

I had this problem recently and ended up writing this little script to handle it. What's nice about this is it will also leave you with correct comment counts per post. If you only delete the duplicate comments without changing this the count will be off.

# First select all comments
$query = "SELECT `comment_ID`, `comment_post_ID`, `comment_content` FROM ".$wpdb->comments." WHERE 1";
$comments = $wpdb->get_results($query);

# Array to hold keeper comment IDs so we dont delete them if there are doops
$keeper_comments = array();

# Now check if each comment has any matching comments from the same post
foreach ($comments as $comment) {
  $query = "SELECT `comment_ID` FROM ".$wpdb->comments." WHERE `comment_ID` != ".$comment->comment_ID." AND `comment_post_ID` = ".$comment->comment_post_ID." AND `comment_content` = '".addslashes($comment->comment_content)."'";
    $matching_comments = $wpdb->get_results($query);
    if ($wpdb->num_rows > 0) {
        foreach ($matching_comments as $matching_comment) {
            if (!in_array($matching_comment->comment_ID, $keeper_comments)) {
                $wpdb->query("DELETE FROM ".$wpdb->comments." WHERE `comment_ID` = ".$matching_comment->comment_ID);
                $wpdb->query("UPDATE ".$wpdb->posts." SET `comment_count` = `comment_count` - 1 WHERE `comment_ID` = ".$matching_comment->comment_ID);
            }
        }
        $keeper_comments[] = $comment->comment_ID;
    }
}

score 0 · Answer 4 · answered Jun 03 '19 at 23:13

I tried all the options above. Unfortunately, grimmdude's didn't work. The solution provided by TI deleted both comments if any were duplicated. I wanted to keep one of the duplicates. With a little help from a friend, this script did the trick.

For anyone that needs the direction, this should be an SQL query run on the DB.

DELETE t1 
FROM wp_comments t1
INNER JOIN wp_comments t2 
WHERE t1.COMMENT_ID < t2.COMMENT_ID AND t1.comment_content = t2.comment_content;

Remove duplicated comments in Wordpress?

4 Answers4