Help with removing dupes - 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)

Help with removing dupes

Author

Topic

uxphreak
Starting Member

38 Posts

Posted - 2005-03-24 : 17:34:45

Hello,

I read "Deleting Duplicate Records" by graz on this site, however, my problem with duplicate records exists in a table that is being replicated (merge). I have some records that exist more than once, but are uniquely identified by their rowguid. I need to remove all duplicates where they exist.

Here's the DDL of the table involved:

CREATE TABLE [dbo].[ProductLocation] (
[ItemID] [char] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[LocID] [char] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Qty] [int] NULL ,
[DestLocID] [char] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ETA] [datetime] NULL ,
[LocationPrice] [money] NULL ,
[IsTaxed] [bit] NOT NULL ,
[StatusID] [int] NOT NULL ,
[MinLvl] [int] NOT NULL ,
[MaxLvl] [int] NOT NULL ,
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL
) ON [PRIMARY]
GO

Here's the query I am using to identify the dupes:

select
pl.ItemID
, pl.LocID
, pl.Qty
, pl.DestLocID
, pl.ETA
, pl.LocationPrice
, pl.IsTaxed
, pl.StatusID
, pl.MinLvl
, pl.MaxLvl
, count (*) AS Dupes
from
productlocation pl
group by
pl.ItemID
, pl.LocID
, pl.Qty
, pl.DestLocID
, pl.ETA
, pl.LocationPrice
, pl.IsTaxed
, pl.StatusID
, pl.MinLvl
, pl.MaxLvl
having
count(*) > 1
order by
count(*) desc
, pl.ItemID
, pl.LocID

Here's some of the data from the above query:

ItemID LocID Qty DestLocID ETA LocationPrice IsTaxed StatusID MinLvl MaxLvl Dupes
---------------- ---------------- ----------- ---------------- ------------------------------------------------------ --------------------- ------- ----------- ----------- ----------- -----------
1009 SOP 0 -1 NULL 2.5000 1 1 0 0 3
1011 SOP 0 -1 NULL 16.2500 1 1 0 0 3
1012 SOP 0 -1 NULL 12.5000 1 1 0 0 3
2500 SOP 0 -1 NULL 12.5000 1 1 0 0 3
2501 SOP 0 -1 NULL 1.7500 1 1 0 0 3
3500 SOP 0 -1 NULL 3000.0000 1 1 0 0 3
3501 SOP 0 -1 NULL 450.0000 1 1 0 0 3
3502 SOP 0 -1 NULL 950.0000 1 1 0 0 3
3503 SOP 0 -1 NULL 1500.0000 1 1 0 0 3
4122 SOP 0 -1 NULL 150.0000 1 1 0 0 3
4123 SOP 149 -1 NULL 145.0000 1 1 0 0 3
4124 SOP 166 -1 NULL 150.0000 1 1 0 0 3
4408 SOP 1871 -1 NULL 40.0000 1 1 0 0 3
4414 SOP 244 -1 NULL 85.0000 1 1 0 0 3
4415 SOP 343 -1 NULL 65.0000 1 1 0 0 3
4416 SOP 234 -1 NULL 85.0000 1 1 0 0 3
4450 SOP 4317 -1 NULL 40.0000 1 1 0 0 3
4451 SOP 4172 -1 NULL 30.0000 1 1 0 0 3
4452 SOP 4568 -1 NULL 20.0000 1 1 0 0 3
4453 SOP 4271 -1 NULL 35.0000 1 1 0 0 3
4454 SOP 198 -1 NULL 125.0000 1 1 0 0 3
4455 SOP 2530 -1 NULL 22.0000 1 1 0 0 3
4602 SOP 2 -1 NULL 155.0000 1 1 0 0 3
4603 SOP 0 -1 NULL 155.0000 1 1 0 0 3
4604 SOP 1 -1 NULL 155.0000 1 1 0 0 3

Thanks for your help,

D

CactusJuice
Starting Member

46 Posts

Posted - 2005-03-24 : 23:03:39

You should still be able to use the logic Graz's article. You're just one step further along in that you alreay have a unique ID.

uxphreak
Starting Member

38 Posts

Posted - 2005-03-28 : 17:28:55

Got it working. Thanks for the help.

D

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources