How can I delete rows that are duplicated on some columns?

Question

I have the following table:

Id color size 1 blue medium 2 blue medium 3 blue small 4 red big

I to exclude all rows that have the color and size duplicated, but different Id. But bear in mind that I don't want to keep one of them, I want to exclude both that have color and size similar, but Id different. So this would be my final table:

Id color size 3 blue small 4 red big

I found this post Delete Duplicate Records in PostgreSQL and this one https://www.postgresqltutorial.com/how-to-delete-duplicate-rows-in-postgresql/. But both of them want to keep one of the rows. I don't want it.

Is there any way?

Are you sure that you want 2, blue, medium in the results? — forpas
– forpas, Commented Jun 19, 2020 at 18:35
Where does (3, red, medium) come from? It's not in the original data — user330315
– user330315, Commented Jun 19, 2020 at 18:36
@a_horse_with_no_name you are absolutely right, so sorry. Just fixed — Dumb ML
– Dumb ML, Commented Jun 19, 2020 at 19:15

forpas · Accepted Answer · 2020-06-19 18:50:16Z

With NOT EXISTS:

select t.* from tablename t where not exists ( select 1 from tablename where id <> t.id and color = t.color and size = t.size );

or with COUNT() window function:

select t.Id, t.color, t.size from ( select *, count(*) over (partition by color, size) counter from tablename ) t where t.counter = 1

If you want to delete the duplicate rows from the table then:

delete from tablename t where exists ( select 1 from tablename where id <> t.id and color = t.color and size = t.size );

See the demo.
Results:

| id | color | size | | --- | ----- | ----- | | 3 | blue | small | | 4 | red | big |

Collectives™ on Stack Overflow

How can I delete rows that are duplicated on some columns?

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related