How do I delete all duplicate rows without a primary key?

Question

I have a table that does not has any unique key or primary key. It has 50 columns and any or all of these columns can be duplicates. How do I delete all duplicate rows but keep the first occurrence?

What RDBMS? Something like this may work: http://stackoverflow.com/questions/15053693/deleting-duplicate-record-in-sql-server — Dan, Feb 11 '14 at 15:47

score 5 · Accepted Answer · answered Feb 11 '14 at 15:54

5

The generic SQL approach is to store the data, truncate the table, and reinsert the data. The syntax varies a bit by database, but here is an example:

create table TempTable as
    select distinct * from MyTable;

truncate table MyTable;

insert into MyTable
    select * from TempTable;

There are other approaches that don't require a temporary table, but they are even more database-dependent.

answered Feb 11 '14 at 15:54

Gordon Linoff

1,242,037
58
646
786

Will this leave the first row of a set of duplicate rows? The RDBMS is mysql – Ranchoddas shyamaldas chanchad Feb 11 '14 at 16:10
@Ranchoddasshyamaldaschanchad . . . How can you distinguish the first appearance from any other if all the columns are the same? – Gordon Linoff Feb 11 '14 at 16:12
I meant if there are three rows where each of the columns are exact duplicates then i want to leave one row and delete the two rows. – Ranchoddas shyamaldas chanchad Feb 11 '14 at 16:20
@Ranchoddasshyamaldaschanchad . . . That is what this approach does. The `distinct` saves one copy of each distinct row in the temporary table, which is then inserted after the original table is emptied. – Gordon Linoff Feb 11 '14 at 17:03

score 1 · Answer 2 · answered Feb 11 '14 at 16:20

If you are using a mysql database use the following command

ALTER IGNORE TABLE tablename ADD UNIQUE INDEX (field1,field2,field3...) This allows duplicates to be removed through the addition of a unique index even with duplicate entries.(the IGNORE keyword is thus used)

If you are using an Oracle database use the following command

Delete from tablename where rowid not in (select min(rowid) from tablename group by row1,row2,row3.....)

How do I delete all duplicate rows without a primary key?

2 Answers2