How to remove duplicate records in mysql

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

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

At first I thought it may delete records by checking id is odd or even but I gave up it as I found there are other new records which has 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

{code type=php}

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

{/code}

Step 2, create a temp table to store duplicate records id

It need to create a temp table to store duplicate records id because if the source id to remove also are target id.
{code type=php}

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;

{/code}

Step 3, remove duplicate records

{code type=php}

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

{/code}

Step 3, remove temp table

{code type=php}
DROP TABLE wp_temp
{/code}

Now the duplicate records have been removed from mysql.

---

Posted from my blog with SteemPress : https://waytowp.com/how-to-remove-duplicate-records-in-mysql/

---