CODEWITHSUNDEEP
sql-server-2008t-sql
user2066036
user2066036

Reputation:

How to delete ALL duplicate records (including the original)

Using SQL Server 2008 tsql, I'm trying to remove ALL the records in a table when a set of values recur. So if my table looked like this:

idcol1col2
 1  A    1  
 2  A    1  
 3  A    2  
 4  B    1  
 5  B    1  
 6  B    2  

Rows 1, 2, 4, 5 would all be deleted.

Upvotes: 0

Views: 731

Answers (2)

anon
anon

Reputation: 

;WITH d AS 
(
  SELECT col1, col2, c = COUNT(*) OVER 
    (PARTITION BY col1, col2 ORDER BY col1)
  FROM dbo.yourtable
)
DELETE d WHERE c > 1;

In fact it can be slightly tidier:

;WITH d AS 
(
  SELECT id, c = COUNT(*) OVER 
    (PARTITION BY col1, col2 ORDER BY col1)
  FROM dbo.yourtable
)
DELETE d WHERE c > 1;

And I'll fess up, I tested the above on SQL Server 2012, however I forgot to change the fiddle to SQL Server 2008. For versions prior to SQL Server 2012, here is one variation:

;WITH d AS 
(
  SELECT col1, col2
  FROM dbo.yourtable AS t
  GROUP BY col1, col2
  HAVING COUNT(*) > 1
)
DELETE t --*
FROM dbo.yourtable AS t
WHERE EXISTS 
(
  SELECT 1 FROM d 
  WHERE col1 = t.col1 AND col2 = t.col2
);
  • You'd think it would be sufficient to just DELETE d; here but you get:

Msg 4403, Level 16, State 1, Line 2
Cannot update the view or function 'd' because it contains aggregates, or a DISTINCT or GROUP BY clause, or PIVOT or UNPIVOT operator.

Upvotes: 3

Sebastian Meine
Sebastian Meine

Reputation: 11783

Try this:

DELETE t
FROM dbo.yourTabe t
JOIN (
  SELECT col1,col2,COUNT(1) cnt
  FROM dbo.YourTable
  GROUP BY col1, col2
  HAVING COUNT(1)>1
) s
ON t.col1 = s.col1
AND t.col2 = s.col2

Upvotes: 0

Related Questions