How to remove duplicate records in MySQL

Today when fix bug for a client I found one of its database tables has duplicate records and that causes plugin error.

See the following screenshot of the pdf_id and cat_id fields there are duplicate records.

At first, I thought it may delete records by checking the id is odd or even but I gave up it as I found there are other new records that have no duplicate ones.

After some leaning, I use the following SQL to remove duplicate records in MySQL and it worked as I want.

Step 1: query duplicate records

Use the following SQL can query out all duplicate records

SELECT MAX(`id`) FROM `wp_bsk_pdf_manager_relationships` GROUP BY `pdf_id` HAVING COUNT(`pdf_id`) > 1

Step 2: Create a temp table to store duplicate records id

It needs to create a temp table to store duplicate records id because if the source id to remove also are target id.

CREATE TABLE `wp_temp`(`id` INT);

INSERT INTO `wp_temp`(`id`) SELECT MAX(`id`) FROM `wp_bsk_pdf_manager_relationships` GROUP BY `pdf_id` HAVING COUNT(`pdf_id`) > 1;

Step 3: remove duplicate records

DELETE FROM `wp_bsk_pdf_manager_relationships` WHERE `id` IN( SELECT * FROM `wp_temp` )

Step 3: remove temp table

DROP TABLE `wp_temp`

Now the duplicate records have been removed from MySQL.