How to delete ALL of the duplicate rows in Posgresql ( not leave a single )-CodePudding

I would like to delete rows from my database that have the same name. I've checked Stackoverflow and found something like this:

DELETE
FROM my_table mt1 USING my_table mt2
WHERE mt1.my_name = mt2.my_name AND mt1.unique_id<mt2.unique_id;

This of course works but leaves one row. I have a request:

If there are rows with duplicate rows I have to remove ALL of them (not leave one).

CodePudding user response：

We can use GROUP BY with a HAVING clause as subquery:

DELETE
FROM my_table 
WHERE my_name IN 
(SELECT
    my_name
FROM
    my_table
GROUP BY
    my_name
HAVING 
    COUNT(*) > 1);

Try out: db<>fiddle

CodePudding user response：

See if you can use this as a template.

drop table if exists #have;

create table #have
(
  ID    [int]
, val   [varchar](10)
)
;

insert into #have
values (1, 'a')
     , (2, 'b')
     , (2, 'b')
     , (3, 'c')

select * from #have;

delete a
from #have a
inner join 
(select id from #have 
 group by id
 having count(*) > 1
) b
 on a.id = b.id
 ;

select * from #have;