How can I delete duplicate rows in a table

Question

I have a table with say 3 columns. There's no primary key so there can be duplicate rows. I need to just keep one and delete the others. Any idea how to do this is Sql Server?

Answer 1

How about:

select distinct * into #t from duplicates_tbl

truncate duplicates_tbl

insert duplicates_tbl select * from #t

drop table #t

Answer 2

The following example works as well when your PK is just a subset of all table columns.

(Note: I like the approach with inserting another surrogate id column more. But maybe this solution comes handy as well.)

First find the duplicate rows:

SELECT col1, col2, count(*)
FROM t1
GROUP BY col1, col2
HAVING count(*) > 1

If there are only few, you can delete them manually:

set rowcount 1
delete from t1
where col1=1 and col2=1

The value of "rowcount" should be n-1 times the number of duplicates. In this example there are 2 dulpicates, therefore rowcount is 1. If you get several duplicate rows, you have to do this for every unique primary key.

If you have many duplicates, then copy every key once into anoher table:

SELECT col1, col2, col3=count(*)
INTO holdkey
FROM t1
GROUP BY col1, col2
HAVING count(*) > 1

Then copy the keys, but eliminate the duplicates.

SELECT DISTINCT t1.*
INTO holddups
FROM t1, holdkey
WHERE t1.col1 = holdkey.col1
AND t1.col2 = holdkey.col2

In your keys you have now unique keys. Check if you don't get any result:

SELECT col1, col2, count(*)
FROM holddups
GROUP BY col1, col2

Delete the duplicates from the original table:

DELETE t1
FROM t1, holdkey
WHERE t1.col1 = holdkey.col1
AND t1.col2 = holdkey.col2

Insert the original rows:

INSERT t1 SELECT * FROM holddups

btw and for completeness: In Oracle there is a hidden field you could use (rowid):

DELETE FROM our_table
WHERE rowid not in
(SELECT MIN(rowid)
FROM our_table
GROUP BY column1, column2, column3... ;

see: Microsoft Knowledge Site

Answer 3

What about this solution :

First you execute the following query :

  select 'set rowcount ' + convert(varchar,COUNT(*)-1) + ' delete from MyTable where field=''' + field +'''' + ' set rowcount 0'  from mytable group by field having COUNT(*)>1

And then you just have to execute the returned result set

set rowcount 3 delete from Mytable where field='foo' set rowcount 0
....
....
set rowcount 5 delete from Mytable where field='bar' set rowcount 0

I've handled the case when you've got only one column, but it's pretty easy to adapt the same approach tomore than one column. Let me know if you want me to post the code.

Answer 4

This is a way to do it with Common Table Expressions, CTE. It involves no loops, no new columns or anything and won't cause any unwanted triggers to fire (due to deletes+inserts).

Inspired by this article.

CREATE TABLE #temp (i INT)

INSERT INTO #temp VALUES (1)
INSERT INTO #temp VALUES (1)
INSERT INTO #temp VALUES (2)
INSERT INTO #temp VALUES (3)
INSERT INTO #temp VALUES (3)
INSERT INTO #temp VALUES (4)

SELECT * FROM #temp

;
WITH [#temp+rowid] AS
(SELECT ROW_NUMBER() OVER (ORDER BY i ASC) AS ROWID, * FROM #temp)
DELETE FROM [#temp+rowid] WHERE rowid IN 
(SELECT MIN(rowid) FROM [#temp+rowid] GROUP BY i HAVING COUNT(*) > 1)

SELECT * FROM #temp

DROP TABLE #temp   

Answer 5

Here's another way, with test data

create table #table1 (colWithDupes1 int, colWithDupes2 int)
insert into #table1
(colWithDupes1, colWithDupes2)
Select 1, 2 union all
Select 1, 2 union all
Select 2, 2 union all
Select 3, 4 union all
Select 3, 4 union all
Select 3, 4 union all
Select 4, 2 union all
Select 4, 2 


select * from #table1

set rowcount 1
select 1

while @@rowcount > 0
delete #table1  where 1 < (select count(*) from #table1 a2 
   where #table1.colWithDupes1 = a2.colWithDupes1
and #table1.colWithDupes2 = a2.colWithDupes2
)

set rowcount 0

select * from #table1

Answer 6

Manrico Corazzi - I specialize in Oracle, not MS SQL, so you'll have to tell me if this is possible as a performance boost:-

1. Leave the same as your first step - insert distinct values into TABLE2 from TABLE1.
2. Drop TABLE1. (Drop should be faster than delete I assume, much as truncate is faster than delete).
3. Rename TABLE2 as TABLE1 (saves you time, as you're renaming an object rather than copying data from one table to another).

Answer 7

Can you add a primary key identity field to the table?

Answer 8

Here's the method I used when I asked this question -

DELETE MyTable 
FROM MyTable
LEFT OUTER JOIN (
   SELECT MIN(RowId) as RowId, Col1, Col2, Col3 
   FROM MyTable 
   GROUP BY Col1, Col2, Col3
) as KeepRows ON
   MyTable.RowId = KeepRows.RowId
WHERE
   KeepRows.RowId IS NULL

Answer 9

I'm not sure if this works with DELETE statements, but this is a way to find duplicate rows:

 SELECT *
 FROM myTable t1, myTable t2
 WHERE t1.field = t2.field AND t1.id > t2.id

I'm not sure if you can just change the "SELECT" to a "DELETE" (someone wanna let me know?), but even if you can't, you could just make it into a subquery.

Answer 10

After you clean up the current mess you could add a primary key that includes all the fields in the table. that will keep you from getting into the mess again. Of course this solution could very well break existing code. That will have to be handled as well.

Answer 11

I'd SELECT DISTINCT the rows and throw them into a temporary table, then drop the source table and copy back the data from the temp. EDIT: now with code snippet!

INSERT INTO TABLE_2 
SELECT DISTINCT * FROM TABLE_1
GO
DELETE FROM TABLE_1
GO
INSERT INTO TABLE_1
SELECT * FROM TABLE_2
GO

Answer 12

This is a tough situation to be in. Without knowing your particular situation (table size etc) I think that your best shot is to add an identity column, populate it and then delete according to it. You may remove the column later but I would suggest that you should keep it as it is really a good thing to have in the table

Answer 13

Add an identity column to act as a surrogate primary key, and use this to identify two of the three rows to be deleted.

I would consider leaving the identity column in place afterwards, or if this is some kind of link table, create a compound primary key on the other columns.