September 10, 2002 at 10:09 am
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
September 10, 2002 at 10:34 am
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('a')
insert into temp_table values('b')
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
September 10, 2002 at 11:21 am
Thanks. It should work for me.
Yin
November 10, 2003 at 3:40 pm
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
Edward M. Sokolove
November 10, 2003 at 3:51 pm
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
November 10, 2003 at 3:55 pm
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
Edward M. Sokolove
November 10, 2003 at 5:23 pm
see today's article also!!
http://www.sqlservercentral.com/columnists/darjun/deletingduplicaterows.asp
Linto
November 17, 2003 at 10:28 pm
Hi Guys,
If i having a duplication problem on table index. anyway to list the duplicated index and delete the duplicate once?
Thanks in advance.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply