Something went wrong in my mysql script now I have loads of duplicate rows. Is there a way I can delete all duplicate rows and leave only one of the duplicates remaning using PHP or raw MySQL?
How do you identify that a row is duplicate? A simple search on Google returned a lot of relevant articles, just look here.
I identify the duplicated using the `domainname` coloum. If you look right at your google search, all of those delete ALL of the duplicates, and do not leave any of them. That why I came here.
Hopefully you have an autonumber primary key which you can use to uniquely identify each row. If so, you would then create an aggregate sub-query which would have 2 fields--domainname which you group by and then the primary key field which you get the Min/Max/First of. This sub-query will be the records you keep. Next, you take your main table and left join to that sub-query and only return records that are not in the sub-query (primary key field is null). This query will identify which rows you will delete. As always, backup your data before attempting any action queries.
If you use MySQL workbench to manage your database tables, there is an option to force unique columns. You can choose/customize from there.