Remove duplicate record in a table

[aauxta]

I think this should be simple but it still got me so I am hoping someone could help me out here:

I have a table with first_name and last_name and some of the first_name and last_name has duplicate records in that table. I am trying to remove the additional ones to it and I was thinking to try on

SELECT * FROM tblMember a
WHERE EXIST
(SELECT * FROM tblMember b
WHERE a.first_name=b.first_name and a.last_name=b.last_name)

to get the addional records back but have no luck, any idea? Thanks in advance

 

[Cagliostro]

This is a query for selecting duplicates.
select first_name, last_name, count(*) from tblMember
group by first_name, last_name havint count(*) > 1
You you have an other record to distinct theese duplicates, it is not a problem to delete them. John Fill

http://ournet.md/~ifilipski/1c.bmp

ivfmd@mail.md

 

[aauxta]

Thanks, and now that I run into another problem with DELETE. We can locate the names for the duplicate entries but what's the best way to remove the extra ones? Do I need to write a batch file in order to get all these done or is there a better way to do it? Appreciate your thoughts.

Thanks,
Aauxta

 

[THoey]

If you are using Oracle, check out a thread that I started and got wonderful answers from Karluk and Carp:

thread186-62406

A little different from your case, but with what is there, I am sure you can resolve your problem. Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[Cagliostro]

aauxta
I believe, yes you need. But you should have a record to distinct dupllicates beteween tehmselves. John Fill

http://ournet.md/~ifilipski/1c.bmp

ivfmd@mail.md

 

[katun]

Try this code,

http://www.katungroup.com/coldfusionsqltips2.htm

If it does not work I will have a more complex version soon I hope.

Bill - katun@mindspring.com