Posted by Plamen Ratchev on 06/13/07 14:29

You would have to define a criteria based on which a row will be kept or
deleted (that is another column or combination of columns that is unique).
Here is just an example based on your sample data (in this case the row with
the MIN name will be kept, but this assumes no duplicate names with the same
phone):

DELETE FROM Address
WHERE EXISTS (
SELECT *
FROM Address AS A
WHERE A.phone = Address.phone
AND A.name < Address.name)

You can easily reverse the above condition to A.name > Address.name to keep
the MAX name.

After you are done you can alter the table and add UNIQUE constraint on the
phone column to prevent duplicate data in the future, something like this:

ALTER TABLE Address ADD CONSTRAINT uphone UNIQUE (phone)


HTH,

Plamen Ratchev
http://www.SQLStudio.com

[Back to original message]