Link to home
Start Free TrialLog in
Avatar of team2005
team2005

asked on

How to remove duplicates when no primary key

Hi!

How can i remove duplicates from a table.
When i dont have a primary key ?
Avatar of Tapan Pattanaik
Tapan Pattanaik
Flag of India image

Hi team2005,

Please check this link, here is answer to your question.

Removing Duplicates from a Table in SQL Server:(Tables with no primary key)

http://www.simple-talk.com/sql/t-sql-programming/removing-duplicates-from-a-table-in-sql-server/
You can do he following way, if the the table is small and is not a part of replication, mirroring and Logshipping and does not have any references(Foreignkey)

DECLARE @t TABLE
(ID INT, C INT, C2 INT, C3 INT)

INSERT INTO @t
SELECT 1,1,2,4

INSERT INTO @t
SELECT 1,1,2,4
INSERT INTO @t
SELECT 1,1,2,4
INSERT INTO @t
SELECT 3,1,2,4
INSERT INTO @t
SELECT 1,2,2,4
INSERT INTO @t
SELECT 1,2,2,4


SELECT * FROM @t

-- in partition by clause, include all columns
SELECT ID,C,C2,C3
INTO #temp
FROM(
SELECT ROW_NUMBER() OVER(PARTITION BY ID, C, C2, C3 ORDER BY ID) RID,*
FROM @t
) T
WHERE RID = 1

DELETE FROM @t

INSERT INTO @t
SELECT * FROM #temp

SELECT * FROM @t


DROP TABLE #temp

Open in new window

hm, @tapanpattanaik that link looks pretty good, Thanks!
ASKER CERTIFIED SOLUTION
Avatar of Ephraim Wangoya
Ephraim Wangoya
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of team2005
team2005

ASKER

thanks