FRIHOST FORUMS SEARCH FAQ TOS BLOGS COMPETITIONS
You are invited to Log in or Register a free Frihost Account!


Deleting duplicate rows in mysql





flatliner
Well I have a mysql table with about 15 colums and around 1250 rows. I would say about 50 of these could be duplicates. I need to be able to delet them easly. The only sorta unqine thing about each row, besides the auto_incerment id is the FILE_NUMBER colum. So maybe there could be some mysql IF statament or somthing like,

if certain file number is found twice, one is delected or somthing?
Any ideas people??

Kind Regards
Ciaran Mc Cann
kv
You can use this sql query to list the ids of duplicate rows. Then use the selected ids to delete the rows. You cannot combine both the operations (select and delete) as a single operation though.

Code:

select id from test_table t1 where FILE_NUMBER in (select FILE_NUMBER from test_table t2 where t2.id < t1.id);

Related topics
E-Cards with PHP & MySQL
Build an online dictionary by PHP/MySQL
mysql/php help please
[MySQL (& php)] += in SQL?
[mySQL] Error I cannot Debug
[man]Azlo tu mismo ShoutBox en PHP y mysql
Searching MySQL Database
mysql gives the wrong result [solved]
Reading MySQL values in reverse
mySQL Query/PHP code - get the highest value...
How to import mysql databases from text files?
How to update multiple rows in mysql with one query?
select x random rows from database
Excel question
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2011 Frihost, forums powered by phpBB.