Delete Duplicate Rows in MySQL (WordPress, Comments)

4 Views

I have a plethora of duplicate comments in my WordPress database, specifically table wp_comments. Of course, those comments have a different IDs. I’d now like to de-dupe those comments based on the field comment_date which would identify all comments posted on the same date and time. I don’t care which one of the duplicates remain.

What SQL query do I have to use to achieve this?

Read More

Thanks!

EDIT: I don’t want to delete a specific comment date across the table, instead I want the database to scan for duplicate dates and remain with only one entry.

Post Views: 4

Related posts

2 comments

  1. You could do a select all query and then loop through those. While in the loop do a query that delete anything that is the same and doesn’t have the ID of current index. Backup first.

    Update:

    I prefer to keep this kind of code in a separate file in the root directory.

    SO make a new file in root and call it whatever you want and then add this code. Run the file AFTER YOU BACKUP your comment and comment meta tables.

    You could do a select all query and then loop through those. While in the loop do a query that  delete anything that is the same and doesn't have the ID of current index. Backup first.

    Update:

    I prefer to keep this kind of code in a separate file in the root directory.

    SO make a new file in root and call it whatever you want and then add this code. Run the file AFTER YOU BACKUP your comment and comment meta tables.

    <?php 
require('./wp-load.php');
global $wpdb; // loads the DB object

$comments = $wpdb->get_results("SELECT * FROM ".$wpdb->prefix."comments");

foreach((array)$comments as $key => $comment)
{
    $id_to_check = $comment->comment_ID; // keep this comment ID
    $get_dupes = $wpdb->get_results("SELECT * FROM ".$wpdb->prefix."comments WHERE comment_content = '".$comment->comment_content."' AND comment_ID != $id_to_check OR comment_date = '".$comment->comment_date."' AND comment_ID != '".$id_to_check."' ");

    foreach((array)$get_dupes as $dkey => $dupe)
    {
         $wpdb->query("DELETE FROM ".$wpdb->prefix."commentmeta WHERE comment_id = '".$dupe->comment_ID."'"); // delete duplicate comment meta
    }

    $wpdb->query("DELETE FROM ".$wpdb->prefix."comments WHERE comment_ID = '".$dupe->comment_ID."'"); // delete duplicate comment

}
echo 'all done!'
?>

  2. first count number of comments …

    example : SELECT COUNT(*) FROM wp_comments WHERE comment_date='blahblah'

    then store the result in a variable … for example $comment_count
    then …

    DELETE FROM wp_comments WHERE comment_date='blahblah' LIMIT N

    replace N with $comment_count-1

Comments are closed.