Large SQL database removing duplicate rows

Question

Large SQL 2008 table with 60 000 000 records, and I have problem with duplicate rows.

This command gives me my duplicate taken from http://support.microsoft.com/kb/139444

SELECT     id, sa_trvalue,  COUNT(*) AS tot  
FROM         msanal   
GROUP BY id, sa_trvalue  
HAVING      (COUNT(*) > 1)

But when I follow through the steps (INTO and DISTINCT) I get not enough memory to complete operation.

If you really need to create a new table with no duplicates, an easy way would be to restrict the query with Where Id >= 0 and Id < 100000 and then just page through until you've covered the entire range. To just get rid, Mr Schmelter has given you a way. — Tony Hopkinson
– Tony Hopkinson, Commented Oct 1, 2013 at 15:48

Tim Schmelter · Accepted Answer · 2013-10-01 15:45:12Z

1

You could try this approach which might need less memory:

WITH CTE AS
(
    SELECT  id, sa_trvalue, 
            rn = ROW_NUMBER() OVER (PARTITION BY id, sa_trvalue ORDER BY id ASC)
    FROM    msanal   
)
DELETE FROM CTE WHERE rn > 1

A common table expression has also the advantage that you can modify it easily to see what you are going to delete. Therefore you just have to change DELETE to SELECT *.

answered Oct 1, 2013 at 15:45

Tim Schmelter

462k79 gold badges719 silver badges980 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Comfortably Numb Over a year ago

You don't really need to select id, sa_trvalue inside of CTE?

Tim Schmelter Over a year ago

@YuriyGalanter: No, just for demonstration purposes.

Mihai · Accepted Answer · 2013-10-01 15:51:41Z

0

delete msanal from msanal m1
where exists
(select null from msanal m2
where m2.sa_trvalue = m1.sa_trvalue and m2.id <> m1.id)

answered Oct 1, 2013 at 15:51

Mihai

26.8k8 gold badges71 silver badges87 bronze badges

Collectives™ on Stack Overflow

Large SQL database removing duplicate rows

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related