Remove duplicated row

How can remove duplicate row in mysql

 

Hello,
Please provide more information about what you perceive as a duplicate row (for instance, what is your table structure and which fields are the same for a duplicate row, table indexes, ...). Also, do you just want to remove those duplicates once, or do you want to prevent duplicates from occurring ever again?
Kind regards,

 

Not quite right, but very simple solution:
1. Get all duplicate rows
2. Remove all superfluous rows in while cycle
For example, if you have table filed "name" and you would remove dublicate rows for this field

$result = mysql_query("select name, count(*) as c from your_table_name group by name having c>1");
while ($row = mysql_fetch_array($result)) {
   mysql_query("delete from your_table_name where name='$row[name]' limit ".($row[c]-1));
}

PHP:

And try to use unique index to prevent duplicate in future.

 

You can try following mysql command as well.

ALTER IGNORE table_name ADD UNIQUE KEY `idx_column_name` (`column_name`);

This will remove duplicate rows identified by column_name.

 

Hi,

Adding unique key is good solution for cases when you need to run the query only once but if you need to do it without altering the table, please check this link : the example is more complex ( duplicates are calculated per site and per number of players ).
Just try modifying it to your real table ( taking care about your real "duplicate" data definition ).
Regards

 

You can either select it all into PHP and then process there to find duplicates, or, much cleaner/quicker:

SELECT field FROM table GROUP BY field HAVING ( COUNT(field) > 1 )

This will show you all items in 'table' where 'field' has duplicate results. If you're happy, you can replace with 'DELETE FROM table ...'