How to remove duplicate records in mysqlsteemCreated with Sketch.

in #utopian-io6 years ago (edited)

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/

Sort:  

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!