# VBForums CodeBank > CodeBank - Other >  [SQL] Elminating duplicate rows from a table

## mendhak

How to remove all duplicate rows from a SQL Server table



```
DECLARE @iErrorVar int,

@vchFirstname INT,

@iReturnCode int,

@vchAddress1 varchar(100),

@iCount int,

@chCount char(3),

@nvchCommand nvarchar(4000)


DECLARE DelDupe CURSOR FOR

SELECT COUNT(*) AS Amount,

liCompanyId

FROM CompanyDetails

GROUP BY liCompanyId

HAVING COUNT(*) > 1


OPEN DelDupe

FETCH NEXT FROM DelDupe INTO @iCount,

@vchFirstname

WHILE (@@fetch_status = 0)

BEGIN



SELECT @iCount = @iCount - 1

SELECT @chCount = CONVERT(char(3),@iCount)

--Now we can build our dynamic ROWCOUNT and DELETE statement:

SELECT @nvchCommand = N'SET ROWCOUNT ' + @chCount + ' DELETE FROM CompanyDetails WHERE liCompanyId = ' +  CONVERT(VARCHAR(20),@vchFirstname) 

EXEC sp_executesql @nvchCommand

FETCH NEXT FROM DelDupe INTO @iCount,

@vchFirstName

END

CLOSE DelDupe

DEALLOCATE DelDupe
```

Substitute 'CompanyDetails' with your table name and liCompanyId with the primary key field of that table.

----------


## Shuja Ali

How about using a simpler version 

```
Delete From 
	TABLE1 
	Where 
		PKFIELD Not In (
				Select 
					Min(PKFIELD) 
				From 
					Table1 
				Group By 
					COLUMN1)
```

 Replace TABLE1 with the table name, PKFIELD with the primary Key field and Column1 with the column you are checking.

----------


## mendhak

Won't that end up deleting nothing, since the duplicate rows have the same pkfield anyways?  :Confused:

----------


## techgnome

Then it's not a true pkfield is it? The pkfield should be enforcing the uniqueness of the row.... that's what makes it the pkey..... or so I thought...

-tg

----------


## Shuja Ali

> Won't that end up deleting nothing, since the duplicate rows have the same pkfield anyways?


Then that Primary Key field will not be a true PK Field.

If you have duplicate records with different primary key then this query will delete those records based on the criteria given in the Group By Clause.  :Smilie:

----------


## CVMichael

This is how I do it:


```
DELETE t
FROM TABLE1 AS t
INNER JOIN (
	SELECT MIN(PK_ID) AS MinPK_ID, MyDupData
	FROM TABLE1
	GROUP BY MyDupData
	HAVING Count(*) > 1
) AS d ON t.MyDupData = d.MyDupData
WHERE d.MinPK_ID <> t.PK_ID
```

I think a join is much faster than a cursor, or "not in", though I did not test your solutions yet, but from experience I know that cursors are slow, and also "not in"

----------

