delete duplicates

Question

Post reply

delete duplicates

yzhang1121

SSC Veteran

Points: 208
More actions
September 10, 2002 at 10:09 am

#58113

Hi, Gregory and everybody,

I visited your SQL Server Examples Web site. It is helpful for me. Thank you for your efforts.

I have a question here. You have a script to identify duplicate records in a table. Do you have a way to delete these duplicates but still keep one record for all duplicates? For example, you have duplicates:

3, item3

3, item3

3, item3

How to delete two of them and keep one record. Of cause, you can delete all three and then insert one. But this is not what I am expecting.

Thank you for your time,

Yin

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Greg Larsen SSC-Insane Points: 20891 More actions · Answer 1

Here is an example that will do that:

-- this example shows a method to get rid of duplicates in a table

create table temp_table (a char(1))

insert into temp_table values('a')

insert into temp_table values('b')

-- show duplicates

select * from temp_table

go

SELECT DISTINCT *

INTO #TEMPTABLE from temp_table

GO

DELETE from temp_table

GO

INSERT INTO temp_table

SELECT * FROM #TEMPTABLE

-- show no dups

select * from temp_table

drop table #temptable

drop table temp_table

-------------------------

Gregory Larsen, DBA

If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

Gregory A. Larsen, MVP

yzhang1121 SSC Veteran Points: 208 More actions · Answer 2

yzhang1121

SSC Veteran

Points: 208

September 10, 2002 at 11:21 am

#436243

Thanks. It should work for me.

Yin

sokolove Mr or Mrs. 500 Points: 546 More actions · Answer 3

Here's how to delete all duplicates and leaving one in the table without reinserting the record.

*********************************************

Table Used in this example

*****************************************

CREATE TABLE [dbo].[Table2] (

[TID] [int] IDENTITY (1, 1) NOT NULL ,

[FName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Table2] WITH NOCHECK ADD

CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED

(

[TID]

) ON [PRIMARY]

GO

*********************************************

Code to delete the records

*********************************************

CREATE PROCEDURE Delete_FName

AS

CREATE TABLE #TempName (PK INT IDENTITY NOT NULL PRIMARY KEY, FName VARCHAR(50), FNCount INT)

INSERT INTO #TempName

(FName,

FNCount)

SELECT

FName,COUNT(FName) AS FNCount FROM table2 GROUP BY FName HAVING COUNT(FName) >1

DECLARE @CheckCount INT

DECLARE @RecordCount INT

DECLARE @DeleteName INT

DECLARE @I INT

DECLARE @FName VARCHAR(50)

SET @RecordCount = (SELECT COUNT(FName) FROM #TempName)

SET @I = 1

WHILE @I <= @RecordCount

BEGIN

SET @FName = (SELECT FName FROM #TempName WHERE PK = @I)

SET @DeleteName = (SELECT TOP 1 TID FROM Table2 WHERE FName = @Fname)

SET @CheckCount = (SELECT COUNT(FName) FROM Table2 GROUP BY FName HAVING FName = @FNAME )

PRINT @RECORDCount

IF @CheckCount > 1

BEGIN

DELETE

Table2

WHERE TID = @DeleteName

SET @CheckCount = (SELECT COUNT(FName) FROM Table2 GROUP BY FName HAVING FName = @FNAME )

Continue

END

ELSE

BEGIN

EXEC Delete_FName

BREAK

END

IF @CheckCount > 1 or @I = @RecordCount

BEGIN

SET @I = @I +1

Continue

END

ELSE

BEGIN

SET @I = @I

Continue

END

Continue

END

GO

Edward M. Sokolove

iadams SSCommitted Points: 1920 More actions · Answer 4

iadams

SSCommitted

Points: 1920

November 10, 2003 at 3:51 pm

#481496

If you have many fields and only some of them are dupes you need another way to do this. Drop me a message and I'll show another method using a cursor where you choose the fields to use for "Duplicate" classification.

Isaiah

-Isaiah

sokolove Mr or Mrs. 500 Points: 546 More actions · Answer 5

sokolove

Mr or Mrs. 500

Points: 546

November 10, 2003 at 3:55 pm

#481497

All you would have to do is modify this stored procedure a bit to add columns (pass in a variable, etc. without using a cursor. I was just giving an example of how to delete 1 column because that is what they were talking about.

Edward M. Sokolove