CODEWITHSUNDEEP
postgresqlduplicates
Dumb ML
Dumb ML

Reputation: 367

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

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?

Upvotes: 0

Views: 93

Answers (1)

forpas
forpas

Reputation: 164099

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   |

Upvotes: 1

Related Questions