[SQL] Elminating duplicate rows from a table

How to remove all duplicate rows from a SQL Server table

Code:

---

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.

How about using a simpler version

Code:

---

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.

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

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

Quote:

---

Originally Posted by mendhak

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

---

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. :)

This is how I do it:

Code:

---

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"