Posted by Seribus Dragon on 06/13/07 13:45

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

[Back to original message]