Deleting duplicate rows in a table

In Sql Server table , there are some duplicate rows
i want to delete the duplicate rows without using Cursors and Temp TableCREATE PROCEDURE RemoveDuplicate AS
Begin
SET NOCOUNT ON
--
DECLARE @.iErrorVar int,
@.CertificateID INT,
@.ClientID INT,
@.iCount int,
@.chCount char(3),
@.nvchCommand nvarchar(4000)
-- set initial environment
SET ROWCOUNT 0
-- Build cursor to find duplicated information

--Change this from a cursor to a table variable. This is an early version
-- of the code before I tarted it up a bit.

DECLARE DelDupe CURSOR FOR
SELECT COUNT(*) AS Amount,
fields that make the row distinct
FROM Table
GROUP BY Fields
HAVING COUNT(*) > 1

OPEN DelDupe
FETCH NEXT FROM DelDupe
INTO @.FieldVars,

WHILE (@.@.fetch_status = 0)
BEGIN
-- Calculate number of rows to delete for each grouping by subtracting
-- 1 from the total count for a given group.

SELECT @.iCount = @.iCount - 1
SELECT @.chCount = CONVERT(char(3),@.iCount)
-- now build the rowcount and delete statements.
SELECT @.nvchCommand = N'SET ROWCOUNT ' + @.chCount +
'DELETE FROM Table ' +
' WHERE All Fields match those in the field variables. = ' + convert(varchar,@.Field1) +
' AND Etc = ' + convert(varchar,@.Field2)

--
-- print @.nvchCommand --Use this to check the syntax
EXEC sp_executesql @.nvchCommand --Comment out until happy.
--
FETCH NEXT FROM DelDupe
INTO @.iCount,
@.CertificateID,
@.ClientID
END
--
CLOSE DelDupe
DEALLOCATE DelDupe
End

This is the easiest way I've found to do removal of dups. Hope it helps.

Cheers
C|||

Quote:

Originally Posted by thithu

In Sql Server table , there are some duplicate rows
i want to delete the duplicate rows without using Cursors and Temp Table

try the following and adapt it to your needs:

create table #t1 (id int identity(1,1), x char(5))
insert into #t1 (x) values ('a')
insert into #t1 (x) values ('a')
insert into #t1 (x) values ('a')
insert into #t1 (x) values ('b')
insert into #t1 (x) values ('b')
insert into #t1 (x) values ('b')
insert into #t1 (x) values ('c')
insert into #t1 (x) values ('d')
insert into #t1 (x) values ('d')

select * from #t1

delete from #t1
where x in (select x from #t1 group by x having count(*) > 1)
and id not in (select min(id) from #t1 group by x having count(*) > 1)

select * from #t1