How can I delete equal rows in a table in SQL?

Question

I have a table with some data inserted in it. The issue is that there are many rows that are equal to other rows and I want to delete them leaving just one of those rows. For example:

Table Person

    name       pet
---------------------------
    Mike       Dog
    Kevin      Dog
    Claudia    Cat
    Mike       Dog
    Mike       Dog
    Kevin      Snake

As you can see, we can see multiple times that Person named Mike has a Dog. But I would like to see it only once. So the output I'll want after update this table is:

    name       pet
---------------------------
    Mike       Dog
    Kevin      Dog
    Claudia    Cat
    Kevin      Snake

How can this be done?

Does table `Person` have an id column? – pilcrow Mar 24 '20 at 13:15 — pilcrow, Mar 24 '20 at 13:15

score 1 · Accepted Answer · answered Mar 24 '20 at 14:16

1

You can do this with exists. In apparent absence of a primary key, system column ctid can be used:

delete from mytable t
where exists (
    select 1
    from mytable t1
    where t1.name = t.name and t1.pet = t.pet and t1.ctid > t.ctid
);

answered Mar 24 '20 at 14:16

GMB

216,147
25
84
135

score 0 · Answer 2 · answered Mar 24 '20 at 13:13

The simplest method is probably to recreate the table:

create table temp_t as 
    select distinct name, pet
    from t;

truncate table t;   -- back it up first!

insert into t (name, pet)
    select name, pet
    from temp_t;

create unique index unq_t_name_pet on t(name, pet);

The last step is to prevent this problem in the future.

How can I delete equal rows in a table in SQL?

2 Answers2