Delete all Duplicate Rows in MySQL

Doing PHP project with MySQL database, I usually meet the problem with duplicate rows value. In the optimization process, I just want to delete all of rows except the first one. I see we have 2 options here:

mysql

  • Fetch all of records to PHP and delete one by one if it is not the first one with the same value
  • Execute one MySQL query to delete

The first way is easy and not a good way to do in big data. We will look at the second one what save time and server resource. We just need to execute these sql query

If you want to keep the row with the lowest id value

Let ‘s use one of these SQL

or

If you want the id value that is the highest

or

Now time to enjoy the result. Check it out and tell us if you meet any problem.

2 comments

  1. It solved my problem quite easy with this tip about mysql. I always do it in PHP but now, no need PHP code at all

  2. It ‘s quite easy to delete and clean our database with your tips. That ‘s cool

Leave a Reply

Your email address will not be published. Required fields are marked *

*

CDN by MaxCDN