Deleting just some duplicates 1

[tjw2006]

I have a table which is meant to contain a certain number of duplicate rows, however due to an error in the flat file which gets imported to populate this table, I've ended up with some duplicates, so for example an entry which should have 19 rows, now has 38. I need to delete just 19 of these rows - how can I do this? the table does have a primary key field.

Thanks.

SQL Server 2000

 

[RiverGuy]

What in your database tells you which 19 of the 38 need to be deleted? What in your database tells you how many duplicate records you need for each "set"? Or is every set supposed to have 19?

If it is just this one isolated incident, you could change the ROWCOUNT:

SET ROWCOUNT 19
DELETE YourTable WHERE PrimaryKeyColumn = SomeValue
SET ROWCOUNT 0

 

[hmckillop]

Riverguy,
I think the statement you posted will only delete 1 record based on what I have read.

tjw2006,
If you have only 19 of the records and a primary key field which is unique I would manually delete the records as an automated script may remove all duplicates (which from what I understand you dont want to do.
For info purposes there is a faq on duplicates which links to this page

http://www.15seconds.com/issue/011009.htm

its worth a read just for info anyway,


"I'm living so far beyond my income that we may almost be said to be living apart

 

[tjw2006]

Thanks for the advice - but it's looking like conventional removing duplicate scripts aren't really geared up for the removal of just some of the duplicates. It's a tricky one!

 

[RiverGuy]

Riverguy,
I think the statement you posted will only delete 1 record based on what I have read.

You're right, my mistake. PrimaryKeyColumn should have been SomeGroupIdentifierColumn

 

[hmckillop]

There are many options available, a clearer understanding of why you wish to have duplicate data in a table would be a good starting point.

An alternative approach could be to put all duplicate data keys into a seperate temp table and then apply your rules to this, but without being able to identify via code which duplicates you need I suspect whatever method will end up a manual process.

Either way, good luck.


"I'm living so far beyond my income that we may almost be said to be living apart

 

[tjw2006]

Well, the table is the 'many' side of a one-to-many configuration. When I say duplicates, they are duplicate rows up to the identity column, which is, thankfully unique - so the identity column is my only possible lifeline I feel.

 

[RiverGuy]

Why don't you just backup your current table, then do a

SELECT DISTINCT <Column List W/O the identity column included>
INTO #TempTable
FROM CurrentTable

DELETE CurrentTable

INSERT INTO CurrentTable 
SELECT * FROM #TempTable

DROP TABLE #TempTable

 

[gmmastros]

I think the RowCount suggestion made by RiverGuy is the correct method to accomplish this.

Here's the idea. You can set the RowCount for the number of columns you want to keep. Then, you delete from the table, matching the column values, and some of the rows will be deleted and some won't.

In the sample code, notice that I add 5 rows for eyecolor = blue, and 6 rows for eyecolor = brown. Now, suppose you want to keep 4 rows for eyecolor = blue, and you don't want to affect any of the brown rows, so that at the end of this process you have 10 rows (4 blues and 6 browns).

[green]-- Create dummy data to test functionality[/green]
Declare @Dups Table(Id Int Identity(1,1), EyeColor VarChar(20))

Insert Into @Dups(EyeColor) Values('Blue')
Insert Into @Dups(EyeColor) Values('Blue')
Insert Into @Dups(EyeColor) Values('Blue')
Insert Into @Dups(EyeColor) Values('Blue')
Insert Into @Dups(EyeColor) Values('Blue')
Insert Into @Dups(EyeColor) Values('Brown')
Insert Into @Dups(EyeColor) Values('Brown')
Insert Into @Dups(EyeColor) Values('Brown')
Insert Into @Dups(EyeColor) Values('Brown')
Insert Into @Dups(EyeColor) Values('Brown')
Insert Into @Dups(EyeColor) Values('Brown')

[green]-- See what's in the table to begin with[/green]
Select * From @Dups

[green]-- Suppose you want to KEEP 4 of the 'Blue' rows


-- Calculate the number of rows to delete.[/green]

Declare @CountToDelete Int
Select @CountToDelete = Count(*) -4 From @Dups Where EyeColor = 'Blue'

If @CountToDelete > 0
  Begin
    [green]-- Only delete if at least one row should be deleted[/green]
    Set RowCount @CountToDelete
    Delete From @Dups Where EyeColor = 'Blue'
  End

Set RowCount 0

-- See what's in the table now.
Select * From @Dups

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom