Question about simple query..

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. columbiamd21044 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. columbiamd21044 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 !What is the criteria that you would use to pic which row to keep?
jaehwang wrote:

Quote:

Originally Posted by

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. columbiamd21044 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. columbiamd21044 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 !
>

|||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.comsql