How to remove duplicates by ID in MySQL?

Question

MySQL

Hello! I want to remove duplicates on the site, since they are easy to find by ID:

SELECT id, COUNT(*) as count
FROM wp_posts
GROUP BY id
HAVING COUNT(*) > 1

Is it possible to somehow change the code so that instead of searching, it deletes 1 copy, leaving the original? Thanks a lot!

Answer 1

Try like this:

CREATE table wp_posts_copy LIKE wp_posts;
ALTER TABLE wp_posts_copy ADD PRIMARY KEY (id);
INSERT INGORE INTO wp_posts_copy SELECT * FROM wp_posts; -- INSERT INGORE вставит только по одному экземпляру id.
DROP TABLE wp_posts;
RENAME TABLE wp_posts_copy TO wp_posts;

Answer 2

sqlinfo.ru/forum/viewtopic.php?id=274
MYSQL. Remove duplicate lines
And always...