CODEWITHSUNDEEP
mysqlsqlwordpress
Esmond Mccain
Esmond Mccain

Reputation: 3

What sql query to use for only deleting duplicate results for wp_comments table?

I need to finish the select query below. The query shows me the count of comments with the same comment_id.I just ultimately want to delete the duplicates and leave the non duplicates alone.This is a wordpress database screenshot of my current query results

SELECT `comment_ID`, `comment_ID`, count(*) FROM `wp_comments` GROUP BY `comment_ID` HAVING COUNT(*) > 1 ORDER BY `count(*)` ASC

example of 2 entries I need to delete one

Upvotes: 0

Views: 109

Answers (3)

O. Jones
O. Jones

Reputation: 108839

First back up your bad table in case you goof something up.

CREATE TABLE wp_commments_bad_backup SELECT * FROM wp_comments;

Do you actually have duplicate records here (duplicate in all columns) ? If so, try this

CREATE TABLE wp_comments_deduped SELECT DISTINCT * FROM wp_comments;
RENAME TABLE wp_comments TO wp_comments_not_deduped;
RENAME TABLE wp_comments_deduped TO wp_comments;

If they don't have exactly the same contents and you don't care which contents you keep from each pair of duplicate rows, try something like this:

CREATE TABLE wp_comments_deduped
SELECT comment_ID,
       MAX(comment_post_ID) comment_post_ID,
       MAX(comment_author) comment_author,
       MAX(comment_author_email) comment_author_email,
       MAX(comment_author_url) comment_author_url,
       MAX(comment_author_IP) comment_author_IP,
       MAX(comment_date) comment_date,
       MAX(comment_date_gmt) comment_date_gmt,
       MAX(comment_content) comment_content,
       MAX(comment_karma) comment_karma,
       MAX(comment_approved) comment_approved,
       MAX(comment_agent) comment_agent,
       MAX(comment_type) comment_type,
       MAX(comment_parent) comment_parent,
       MAX(user_id) user_id
  FROM wp_comments
 GROUP BY comment_ID;
RENAME TABLE wp_comments TO wp_comments_not_deduped;
RENAME TABLE wp_comments_deduped TO wp_comments;

Then you'll need to doublecheck whether your deduplicating worked:

SELECT comment_ID, COUNT(*) num FROM wp_comments GROUP BY comment_ID;

Then, once you're happy with it, put back WordPress's indexes.

Pro tip: Use a plugin like Duplicator when you migrate from one WordPress setup to another; its authors have sorted out all this data migration for you.

Upvotes: 1

Greg
Greg

Reputation: 94

I'm not clear on why there appear to be two different fields both named 'column_ID' from the same table, but I believe this will delete only the first of the two identical records. Before running a DELETE statement, however, be sure to make a backup of the original table.

DELETE
   TOP 1 * 
FROM 
   'wp_comments'
WHERE 
   comment_ID IN
(
SELECT
  comment_ID,
  r,
  (comment_ID + '_' + r) AS unique
FROM 
   (
   SELECT
      `comment_ID`, 
       `comment_ID`,
       RANK() OVER (PARTITION BY 'comment_id' ORDER BY 'comment_id') AS r
   FROM
      'wp_comments'
   )
WHERE
   r>1
)

Upvotes: 0

eshirvana
eshirvana

Reputation: 24633

I would recommand add a unique key to the table make it auto incremental call it tempId , so you would be able to to distinguish between one duplicate set, use below query to remove duplicate copies and at the end remove that '`tempid' column:

DELETE FROM `wp_comments`
WHERE EXISTS (
    SELECT  `comment_ID` , MIN(`tempid`) AS `tempid`
    FROM `wp_comments` as `dups`
    GROUP BY `comment_ID` 
    HAVING 
        COUNT(*) > 1 
        AND  `dups`.`comment_ID` = `wp_comments`.`comment_ID`
        AND `dups`.`tempid` = `wp_comments`.`tempid`
        )

Upvotes: 0

Related Questions