Duplicated comments removing – workaround

A lot of duplicated comments obtained in WP after import LJ.

To remove duplicates I used MySql scripts.

Potentially some duplictes can exist after running script, but in practice I did not experienced with this. This is in case when there are two duplicated comment: one with minimum id among duplicates, other – with child comments under it.

If you are going to apply it, you should should beckup your data first.

SQL scripts follow:

CREATE TEMPORARY TABLE __comments_dup AS
(
SELECT DISTINCT wp1.<code>comment_ID</code>
FROM <code>wp_comments</code> AS wp1
INNER JOIN <code>wp_comments</code> AS wp2
WHERE wp2.comment_ID < wp1.comment_ID
AND wp2.comment_content = wp1.comment_content
AND wp2.comment_date_gmt = wp1.comment_date_gmt
AND wp2.comment_parent = wp1.comment_parent
AND wp2.comment_author = wp1.comment_author
AND
(SELECT COUNT(<code>comment_ID</code>) FROM <code>wp_comments</code> AS wp3 WHERE wp3.comment_parent = wp1.comment_ID) = 0
);

DELETE FROM wp_comments WHERE comment_ID IN (SELECT comment_ID FROM __comments_dup);

DROP TEMPORARY TABLE __comments_dup;

UPDATE wp_posts SET comment_count = (SELECT COUNT(*) FROM wp_comments WHERE
wp_comments.comment_post_ID = wp_posts.ID AND wp_comments.comment_approved = 1)

[Moderator Note: Please post code & markup between backticks or use the code button. Your posted code may now have been permanently damaged by the forum’s parser.]