Remove duplicated comments in WordPress?

4 comments

1. 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
   

   Log in to Reply

2. 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”….

   Log in to Reply

3. 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;
       }
   }
   

   Log in to Reply

4. 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;
   

   Log in to Reply