help on find duplicates and delete procedure

Hi everybody I need help on finding duplicates and deleting the duplicate record depending on name and fname , deleting the duplicates and leaving only the first one.

my PERSON table is this below:

ID name fname ownerid id2

1 a b
2 c c
3 e f
4 a b 1 10
5 c c 2 11

I have this query below that returns records 1 and 4 and 2 and 5 since they have the same name and fname

select * from ( Select name ,fname, count(1) as cnt from PERSON group by
name,Fname ) where cnt > 1

ID name fname ownerid id2

1 a b
4 a b 1 10

2 c c
5 c c 2 11

With this result I need to delete the second record of each group but update the first records with the ownerid and id2 of the second record that would be deleted... I don't know how to proceed with this..

thanks
alexhow do you know which one is the "second" record?

and why go to the trouble of updating the one you keep? why not delete the one that doesn't have values in ownerid and id2?|||how do you know which one is the "second" record?

and why go to the trouble of updating the one you keep? why not delete the one that doesn't have values in ownerid and id2?

the second record is the one with ownerid and id2 well the reason behind is that the second record is inserted from another table and the id of the first one is the one i need due to relationships with other tables|||I see trouble ahead ;) If you update the missing data in the "first" of the duplicate rows, then how are you going to figure out which row is which when you later attempt to delete the "second" row?

Keep in mind that your question/description is rife with suggestions of "order" in your data. Beware "order" where there is none but that which is artificially created. (I need to get a job writing fortune cookies).

Probably you will need to add a column or work in a temporary table so that you can artificially add a "flag" of some type so you know which rows are the "good" rows and which ones you will later slaughter mercilessly after brazenly and carelessly using them only for their data.|||I see trouble ahead ;) If you update the missing data in the "first" of the duplicate rows, then how are you going to figure out which row is which when you later attempt to delete the "second" row?

Keep in mind that your question/description is rife with suggestions of "order" in your data. Beware "order" where there is none but that which is artificially created. (I need to get a job writing fortune cookies).

Probably you will need to add a column or work in a temporary table so that you can artificially add a "flag" of some type so you know which rows are the "good" rows and which ones you will later slaughter mercilessly after brazenly and carelessly using them only for their data.

yes thanks for your suggestion will try it then...|||this might work for u ...
[code]
delete from Table1 where ID in
(select ID from Table1, (Select name ,fname, count(*) as cnt from Table1 group by name,Fname having count(*) > 1) as xx
where Table1.name=xx.name and Table1.fname=xx.fname and Table1.ownerid is not null and Table1.id2 is not null)
[code]

Help needed in SQL Query Search!

Hi everyone,

I'm trying to implement SQL Server database search. The details are:-
1. I have table called EMPLOYEE has FNAME,LNAME etc cols.
2. User might look for any employee using either FNAME or LNAME
3. I have search box in asp.net where user could enter search string
The sample data:
FNAME LNAME
abc george
def george
rkis lita
rose lita

The query i wrote:
SELECT * FROM EMPLOYEE WHERE lname like '%' + searchArg + '%'
My problem is:-
1. let's say user is looking for employee "george"; In search string instead of typing actual word "george", user could type "jeorge"; because the name pronounce or sounds like similar.
Same thing with user could type "leta" instead of "lita". Again these are all similar sounds.

When you look for "jeorge" in GOOGLE; it says "did you mean george"; i would like implement something like that. somewhere i saw SOUNDEX would do what i am looking for; but i no luck for me.

Is this possible anyway in T-SQL or Fulltext search.

Your help is greatly appreciated.

Thanks
Bob

Bob,
You were on the right track with Soundex, here's how you can use it. Also look into difference.
Sample data: (pubs)

au_id au_lname au_fname

---- ------------ -------

409-56-7008 Bennet Abraham

648-92-1872 Blotchet-Halls Reginald

238-95-7766 Carson Cheryl

722-51-5454 DeFrance Michel

712-45-1867 del Castillo Innes

427-17-2319 Dull Ann

213-46-8915 Green Marjorie

527-72-3246 Greene Morningstar

472-27-2349 Gringlesby Burt

My Queries:

select*from authorswheresoundex(au_lname)=soundex('Benet')

Returns:
au_id au_lname au_fname

---- ------------ -------

409-56-7008 Bennet Abraham

select au_id,au_lname,au_fnamefrom authorswheredifference(au_lname,'whit')> 3

Returns:
au_id au_lname au_fname

---- ------------ -------

172-32-1176 White Johnson

Hope this helps, as you can see here it is definitely possible to get the "similar" functionality. Check books online for more info on difference, iirc it returns one of a number of different values that state how close or different the words are.
Scott