4

I'm looking for the most efficient way to remove duplicates from a table where id's are unique but there are equal rows when you check other columns.

Example:

id   name   age   x
1   peter   25    II
2   peter   25    II

The table has tens of thousands of rows.

Greenonline
  • 231
  • 1
  • 4
  • 15
Milano
  • 143
  • 1
  • 6

2 Answers2

5

Supposing you have no foreign keys referencing that table, you could do something like 

create table some_table as
select min(id), name, age, x
from t
group by name, age, x

then you can drop the old table, rename the new table so that it has the same name as the old one before, and create indexes and other things you need on that table.

zgguy
  • 1,143
  • 7
  • 10
2

Even if the question is 6 years old

I had a similar need and I used something like that

    DELETE 
    FROM t 
    where
    id||name||age 
    NOT IN
    (SELECT
    min(id)||name||age
    FROM t 
    GROUP BY id, name, age)
ontheroad
  • 21
  • 3