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/
Thank you for your contribution @ascending.taurus.
After analyzing your tutorial we suggest the following:
Your tutorial is quite short for a good tutorial. We recommend you aim for capturing at least 2-3 concepts.
In a tutorial it is important to have the theory about what it will explain. A tutorial is not just steps like solving something.
We suggest you use the tutorials template. Link.
Link an example of a good tutorial.
In a tutorial it is very important to detail very well the subject that is explaining to the reader to understand well what you are developing.
Your tutorial is just a solution to a problem that can be solved in several ways.
Please in the next tutorial pay attention to the points that have been suggested here. Thank you.
Need help? Write a ticket on https://support.utopian.io/.
Chat with us on Discord.
[utopian-moderator]
Thank you for your review, @portugalcoin! Keep up the good work!