delete Duplicate entries - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

delete Duplicate entries

Author

Topic

enak
Starting Member

34 Posts

Posted - 2007-02-11 : 16:27:02

I have mulitple duplicate entries in a table. I need to be able to one entry for each duplicate.

I have tried to do it through Enterprise Manager but I get an error. How can I do this?

Thanks,
enak

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-02-11 : 18:23:27

this is one of the most common questions.

try typing "remove duplicates" into the form here: http://www.sqlteam.com/forums/search.asp

there's also this article:

http://www.sqlteam.com/item.asp?ItemID=3331

www.elsasoft.org

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2007-02-11 : 19:32:37

omg! there is even a kb entry to solve this bug/(feature)

http://support.microsoft.com/kb/139444

rockmoose

" this is one of the most common questions."
scary...

-----
Is this a conspiracy?
2007, and we STILL have duplicates?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=6256

enak
Starting Member

34 Posts

Posted - 2007-02-11 : 19:37:42

Thanks for the info. I found the answer but I can not get the sql to run.

I have 2 fields that are type text and will not work with the DISTINCT key word. How can I make this work?

select distinct file_num,app_ssn,client_num,client_name,sub_name,lastname,firstname,middlename,
maidenname,other_names,dob,dl_st,dl_num,gender,race,phone_num,curr_addr,curr_city,yrs_city,there_since,
prev_addr,prev_city,years_prev,/*other_cities,notes,*/lastcalled,svcs_stage1,svcs_stage2,svc_date,svc_time,
sub_location,written_up,request_by,position,used_since,harrisCo
into #holding
from applicant_backup

I have commented out the 2 text fields.

Thanks,
enak

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2007-02-11 : 19:44:29

....
select distinct file_num,app_ssn,client_num,client_name,sub_name,lastname,firstname,middlename,
maidenname,other_names,dob,dl_st,dl_num,gender,race,phone_num,curr_addr,curr_city,yrs_city,there_since,
prev_addr,prev_city,years_prev,/*other_cities,notes,*/lastcalled,svcs_stage1,svcs_stage2,svc_date,svc_time,
sub_location,written_up,request_by,position,used_since,harrisCo
--into #holding
from applicant_backup
...

What is the unique key of your table?

You will have to decide upon a rule on which data to keep for each field (including the text fields), based upon that key.

rockmoose

enak
Starting Member

34 Posts

Posted - 2007-02-11 : 19:48:43

There is no key to the table. Once I get the mess cleaned up I will make file_num the key.

But until then I need to be able to deal with the TEXT fields.

I am far from a DBA so be patient. I just inherited this mess.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2007-02-12 : 03:06:55

You can cast the text fields to varchar.
cast(notes,varchar(8000))

If the text fields do not exceed that length then that is ok.
check their length:

select max(datalength(notes)),max(datalength(other_cities)) from applicant_backup.

rockmoose

enak
Starting Member

34 Posts

Posted - 2007-02-12 : 06:34:55

That worked. Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources