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?

Answer 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
);