query for delete duplicate data

my table is test
id name
-------------------------
1 prince
2 king
3 queen
4 prince
5 prince
6 prince

I want to delete repeated name like prince repeated many times. so i want to remove repeated data and remain only one time every name.

my query is :
$sql = mysql_query("delete from test where id in (select id from (SELECT id FROM test GROUP BY name having COUNT(*) > 1)temp");
but it's not working........
plz help me................

 

why not create a temp table and just to a INSERT INTO newdatabase ('name') SELECT DISTINCT name from database

your query looks like it will delete all duplicates

 

I think you should consider altering your table and adding a unique key restraint on the name column.

Then you could do as pmf123 suggests to populate that new table.

That would solve your current problem and keep this issue from recurring in the future.

 

One way is to create a view with no duplicates then put it back into the cleaned out table. There are other ways I'm a bit rusty use to do this years ago.

 

DELETE E1 from TEST E1, TEST E2 where e1.name = e2. name and e1.id > e2.id

 

There are lots of ways to do it, as you can see. It all depends on if you need to do it more than once and fix the problem permanently going forward, of if you just want to do a one-time cleanup.

 

You can use distinct keyword in your query to retrieve only unique values.