Find duplicate values

[johnie]

Hi. I'm cleaning up a table and i want to find and delete the multiple values.
The fields are id(autonumber), pid and cid
Each cid should exist only once for every pid.

I want to find and delete the multiple entries of the same cid for each pid and leave only one. Of course the cid can exist for other pid

What's the query?

Thanks,
John

[Pig]

I think the simplest method would be to select everything, and then for every line insert it into an array. That way, an duplicate entries just end up replacing the old ones.

eg: if you set $array[4]=10 100 times, you will still end up with the same thing.

then you can just empty the entire table and re-insert everything from the array.

[johnie]

Thanks but i don't want PHP but only plain MySQL.
Through PHP it's easy to do it.

Thanks,
John

[dnszero]

My first response would be to do it like this:

CREATE TABLE TempTable SELECT DISCTINT * FROM OriginalTable;

DROP TABLE OriginalTable;

ALTER TABLE TempTable RENAME OriginalTable;

You could prolly also do it with subquerries, but the above seems easiest. You may need to play with the syntax and whatnot, that's just for example.

Also, why not just prevent duplicates from ever being inserted in the first place? That should be easy and more efficient too.

[johnie]

Thanks for the idea.
I am preventing duplicates now.
I just want to clear old values from an old table.

Thanks,
John