Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Deleting duplicate records except for one

3 posts in Windows NT Last posting was on 2001-05-22 14:23:49.0Z
Jorge Posted on 2001-05-21 23:35:21.0Z
From: "Jorge" <iamjas99@hotmail.com>
Subject: Deleting duplicate records except for one
Date: Mon, 21 May 2001 19:35:21 -0400
Lines: 10
X-Newsreader: Microsoft Outlook Express 4.72.3110.5
X-MimeOLE: Produced By Microsoft MimeOLE V4.72.3110.3
Message-ID: <leMuZGl4AHA.249@forums.sybase.com>
Newsgroups: sybase.public.sqlserver.nt
NNTP-Posting-Host: hboc.com 139.177.224.128
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:707
Article PK: 1087314

Hi. I have a table that gets data from a bcp process. This usually ends up
inserting data into the table that may already be there (duplicates). This
unfortunately cannot be prevented. How can I get rid of all but one of the
duplicates (IOW - I have 10 rows that are the same. I would like to delete
nine of them)? Could this be accomplished in a single DELETE statement?
Thanks.

Jorge


Jim Egan Posted on 2001-05-22 04:39:19.0Z
From: Jim Egan <dbaguru@eganomics.com>
Subject: Re: Deleting duplicate records except for one
Date: Mon, 21 May 2001 22:39:19 -0600
Message-ID: <MPG.15739b5d754fb51098b390@forums.sybase.com>
References: <leMuZGl4AHA.249@forums.sybase.com>
Reply-To: eganjp@compuserve.com
X-Newsreader: MicroPlanet Gravity v2.50
Newsgroups: sybase.public.sqlserver.nt
Lines: 20
NNTP-Posting-Host: c1420400-b.hiland1.co.home.com 65.7.153.228
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:706
Article PK: 1087313


iamjas99@hotmail.com wrote...
> Hi. I have a table that gets data from a bcp process. This usually ends up
> inserting data into the table that may already be there (duplicates). This
> unfortunately cannot be prevented. How can I get rid of all but one of the
> duplicates (IOW - I have 10 rows that are the same. I would like to delete
> nine of them)? Could this be accomplished in a single DELETE statement?
> Thanks.
>
> Jorge
>
>
>

You may want to consider looking at the ignore_dup_row option on the CREATE INDEX
statement. It could be a quick solution to you problem.
--
Jim Egan [TeamSybase]
Senior Consultant
Sybase Professional Services

Sybase TechWave 2001 http://www.sybase.com/events/techwave2001
August 12th - 16th, San Diego


Jorge Posted on 2001-05-22 14:23:49.0Z
From: "Jorge" <iamjas99@hotmail.com>
References: <leMuZGl4AHA.249@forums.sybase.com> <MPG.15739b5d754fb51098b390@forums.sybase.com>
Subject: Re: Deleting duplicate records except for one
Date: Tue, 22 May 2001 10:23:49 -0400
Lines: 32
X-Newsreader: Microsoft Outlook Express 4.72.3110.5
X-MimeOLE: Produced By Microsoft MimeOLE V4.72.3110.3
Message-ID: <YA9Y42s4AHA.241@forums.sybase.com>
Newsgroups: sybase.public.sqlserver.nt
NNTP-Posting-Host: hboc.com 139.177.224.128
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:704
Article PK: 1087312

Thanks. That does the trick. I can still perform the bcp processes
successfully without adding duplicate data, only new data.

Jim Egan wrote in message ...
>iamjas99@hotmail.com wrote...
>> Hi. I have a table that gets data from a bcp process. This usually ends
up
>> inserting data into the table that may already be there (duplicates).
This
>> unfortunately cannot be prevented. How can I get rid of all but one of
the
>> duplicates (IOW - I have 10 rows that are the same. I would like to
delete
>> nine of them)? Could this be accomplished in a single DELETE statement?
>> Thanks.
>>
>> Jorge
>>
>>
>>
>You may want to consider looking at the ignore_dup_row option on the CREATE
INDEX
>statement. It could be a quick solution to you problem.
>--
>Jim Egan [TeamSybase]
>Senior Consultant
>Sybase Professional Services
>
>Sybase TechWave 2001 http://www.sybase.com/events/techwave2001
>August 12th - 16th, San Diego