Deleting Duplicate Entries from a Mysql Database


I've got a table with three columns, obj1, obj2 (both varchars) and the distance between the objects. Unfortunately the way the data was constructed, I've double the number of entries, for example,

obj1      obj2       distance
c1        c2         10.5
c2        c1         10.5

Want I want is to be able to delete one of the entries listed. I've thought and tried to use the Exists clause, but had no luck. I'm wondering if this requires a stored procedure?

Any help would be gratefully received! Jim

If you can guarantee that every row has a "duplicate" with obj1 and obj2 values reversed, then you can remove one such row for each duplicate by doing

DELETE FROM dist WHERE obj1 > obj2

where dist is the name of your table.

If your table has rows where obj1 equals obj2, then you could make a unique index on (obj1,obj2):

ALTER IGNORE TABLE dist ADD UNIQUE INDEX dist_index (obj1,obj2)

The above command will drop rows from the table whenever the unique index constraint is not satisfied. (The first row where obj1 equals obj2, the row will be kept because the unique index constraint is still satisfied. The second row where obj1 equals obj2 will be dropped, because the second row contradicts the uniqueness constraint.)

You can choose to keep the unique index, or, if you wish to drop it, the command would be:

ALTER TABLE dist DROP INDEX dist_index