Question about simple query..

[jaehwang]

How are you ? Please give me an advice :)
It's been bothering me for three days. I'm new SQL user.

I have the following table, which has name, address, city,state,zip
code, and phone number.
kim 3134 n. chatham ellicott city md 21042
410-222-2222
justin 3134 n. chatham rd. ellicott city md 21042
410-222-2222
hong 4343 antonio ln. ellicott city md 21042
555-341-3412
woo 1123 cedar ln. columbia md 21044 410-111-1358
The problem is that the table should not contain the same phone
number.
Phone number 410-222-2222 is duplicated.

How can I erase that extra data, and get the table like this ? :

kim 3134 n. chatham ellicott city md 21042
410-222-2222
*******************the data that has same phone number is
gone*****************
hong 4343 antonio ln. ellicott city md 21042
555-341-3412
woo 1123 cedar ln. columbia md 21044 410-111-1358

First, I used this query, but it turned out all of my data was gone. :
(

delete FROM address WHERE exists (
select * from address where address.phone = address.phone
);

Please help !

[Seribus Dragon]

What is the criteria that you would use to pic which row to keep?
jaehwang wrote:

How are you ? Please give me an advice :)
It's been bothering me for three days. I'm new SQL user.

I have the following table, which has name, address, city,state,zip
code, and phone number.
kim 3134 n. chatham ellicott city md 21042
410-222-2222
justin 3134 n. chatham rd. ellicott city md 21042
410-222-2222
hong 4343 antonio ln. ellicott city md 21042
555-341-3412
woo 1123 cedar ln. columbia md 21044 410-111-1358
The problem is that the table should not contain the same phone
number.
Phone number 410-222-2222 is duplicated.

How can I erase that extra data, and get the table like this ? :

kim 3134 n. chatham ellicott city md 21042
410-222-2222
*******************the data that has same phone number is
gone*****************
hong 4343 antonio ln. ellicott city md 21042
555-341-3412
woo 1123 cedar ln. columbia md 21044 410-111-1358

First, I used this query, but it turned out all of my data was gone. :
(

delete FROM address WHERE exists (
select * from address where address.phone = address.phone
);

Please help !

[Plamen Ratchev]

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